Блог

В настоящее время я работаю над запросом, который должен возвращать все строки из CONCENTRATOR таблицы. Однако он должен быть отсортирован по всем столбцам концентратора, а также по названию отдела и имени типа.

Вот столбцы концентратора :

 CONCENTRATOR_ID
NAME
INTERNALADDRESS
TYPE_ID
DEPARTMENT_ID
  

TYPE_ID и DEPARTMENT_ID связаны соответственно с DEPARTMENT таблицей и TYPE table, причем оба имеют NAME столбец.

Вот ограничения :

• концентраторы можно сортировать по идентификатору, имени, адресу, названию типа и названию отдела
• разные названия отделов (если один и тот же концентратор имеет 2 отдела, возвращает только одну строку)

Чтобы возобновить, мне понадобилось бы что-то вроде SELECT * для столбцов концентратора, и DISTINCT department.name также, но кажется сложным… Я перепробовал множество запросов, но не смог найти ни одного работающего. Кто-нибудь может мне помочь, пожалуйста?

Запрос, который я ищу, должен быть примерно таким:

 SELECT DISTINCT d.NAME as "department.name", t.NAME as "type.name", *
FROM "CONCENTRATOR" c
LEFT OUTER JOIN "CONCENTRATOR_GROUP" USING(CONCENTRATOR_ID)
LEFT OUTER JOIN "GROUP" g USING(GROUP_ID)
LEFT OUTER JOIN "TYPE" t USING(TYPE_ID)
LEFT OUTER JOIN "DEPARTMENT" d USING(DEPARTMENT_ID)
ORDER BY TRIM(UPPER(c.name)) ASC
  

Здесь следует отметить несколько моментов. Мне действительно не нравятся «естественные соединения», поскольку они просто маскируют полезные детали, на мой взгляд, поэтому я их не использовал. Я должен был предположить, что таблица «GROUP» соединяется через CONCENTRATOR_GROUP для примера этой недостающей детали.

Название таблицы «GROUP» — не лучшая идея, поскольку это очень часто используемое зарезервированное слово. Я бы не рекомендовал использовать такое слово в качестве имени таблицы. Из-за этого «ГРУППА» заключена в кавычки (по моему опыту, в Oracle не принято заключать имена объектов в кавычки).

Вы говорите о «distinct» так, как будто у него есть какое-то волшебное качество, которое я должен интуитивно понимать. Это не так, и я этого не делаю. Допустим, есть только 2 отдела, оба из которых также «разные»

DeptX DeptY

Итак, теперь давайте предположим, что есть 2 концентратора, оба они тоже «distinct»:

ConcenA ConcenB

Оба концентратора используются в обоих отделах, поэтому мы создаем этот запрос:

 select distinct 
c.name as c_name, d.name as d_name
from concentrators c 
inner join departments d on c.dept_id=d.dept_id 

The result is:

ConcenA DeptX
ConcenB DeptX
ConcenA DeptY
ConcenB DeptY
  

Все 4 строки «distinct»

Дело в том, что «select distinct» — это «оператор строки», то есть он рассматривает всю строку, чтобы определить, отличается ли какая-либо часть строки от всех других строк. Для «выбора distinct» нет никаких тонкостей или опций, это всегда работает одинаково (по всей строке). Итак, имея это в виду, мы теперь знаем, что «выбрать distinct» просто не будет правильным методом (и из-за технического определения distinct вы также можете почувствовать, что это не самый лучший способ описать вашу проблему).

Итак, поскольку «select distinct» не является правильным методом, обычно можно обратиться к этим методам: «group by» или «row_number()», потому что они действительно предоставляют нам тонкости и опции.

Теперь вы не объяснили, почему или как вы выбрали бы только один отдел (на самом деле, для меня это звучит странно, что вы выбрали бы только один), но ниже я предлагаю вам способ сделать это с помощью row_number(), и используемая «тонкость» заключается в порядке, при котором номер 1 присваивается названию первого отдела в алфавитном порядке, все остальные отделы получают больше 1; и это происходит для каждого идентификатора КОНЦЕНТРАТОРА, потому что row_number() «разделяется» этим полем.

     SELECT
      department_name
    , type_name
    , NAME
    , CONCENTRATOR_ID
    , INTERNALADDRESS
    , TYPE_ID
    , DEPARTMENT_ID
FROM (

            SELECT
                  d.NAME                           AS department_name
                , t.NAME                           AS type_name
                , c.CONCENTRATOR_ID
                , c.NAME
                , c.INTERNALADDRESS
                , c.TYPE_ID
                , c.DEPARTMENT_ID
                , ROW_NUMBER() OVER (PARTITION BY c.CONCENTRATOR_ID
                                     ORDER BY d.NAME, t.NAME, c.NAME) AS RN
            FROM CONCENTRATOR c
                  LEFT OUTER JOIN CONCENTRATOR_GROUP cg
                        ON c.CONCENTRATOR_ID = cg.CONCENTRATOR_ID
                  LEFT OUTER JOIN "GROUP" g
                        ON cg.GROUP_ID = g.GROUP_ID
                  LEFT OUTER JOIN TYPE t
                        ON c.TYPE_ID = t.TYPE_ID
                  LEFT OUTER JOIN DEPARTMENT d
                        ON c.DEPARTMENT_ID = c.DEPARTMENT_ID
      ) sq
WHERE RN = 1 /* HERE is where we restrict output to one department per concentrator */
ORDER BY
      NAME ASC
    , CONCENTRATOR_ID
;
  

У меня нет причин изменять тип соединений, поскольку вы можете видеть, что они остаются левыми внешними соединениями, но я подозреваю, что для всех или некоторых из них может не быть веской причины. Используйте более эффективное ВНУТРЕННЕЕ соединение, если можете.