Сначало группировка, потом объединение

[KBO]

Доброе время суток,

У меня есть таблица EQUIPMENT (оборудование) и CERTIFICATE (сертификаты) к этому оборудованию (каждое оборудование имеет несколько сертификатов)
Имелся также и рабочий запрос (до некоторого времени) для выборки просроченных сертификатов, который выводит на экран список оборудования и один сертификат к каждому оборудованию, если в таблице CERTIFICATE поле DISPLAY = 1 (1-это отображение, 0 – скрытие сертификата)

Select eq.ID, eq.NAME,  eq.CONTROL_MIN_DATE, eq."TYPE", eq.NUMBER, ….
 u.FULL_NAME as u_EQUIPMENT_USER, ld.NAME_SHORT as l_LABORATORY_DOC, ud.FULL_NAME as u_USER_DOC,
 (case when c.NUMBER_CERT = '-1' then '' else c.NUMBER_CERT end) as c_NUMBER_CERT,  c.DATE_OPERATION as c_DATE_OPERATION, c.CALIBRATION_INTERVAL as c_CALIBRATION_INTERVAL
 from EQUIPMENT eq
 left join LABORATORY l on l.id = eq.EQUIPMENT_LABORATORY_ID
 left join LABORATORY ld on ld.id = eq.LABORATORY_DOC_ID
 left join CERTIFICATE c on c.DEVICE_ID_CL = eq.ID and c.DISPLAY = 1
  left join USERS_DATA u on u.ID = eq.EQUIPMENT_PARENT_USER_ID
  left join USERS_DATA ud on ud.ID = eq.USER_DOC_ID
    where eq.ARCHIVE in (1)
  and (((eq.CONTROL_MIN_DATE < '02.02.2023' and not eq.CONTROL_MIN_DATE is null)  or (eq.CONTROL_MIN_DATE is null and eq.COUNT_CERT <> -1)  or (eq.CONTROL_MIN_DATE is null and eq.COUNT_CERT >= 0)  or (eq.COUNT_CERT_REQUIRE <> eq.COUNT_CERT))
  and ((eq.CONTROL_MIN_DATE < '02.02.2023' and not eq.CONTROL_MIN_DATE is null)  or (eq.CONTROL_MIN_DATE is null and eq.COUNT_CERT <> -1)  or (eq.CONTROL_MIN_DATE is null and eq.COUNT_CERT >= 0)  or (eq.COUNT_CERT_REQUIRE <> eq.COUNT_CERT)))

and ((c.CONTROL = 1 and eq.PASSPORT = 0) or ((c.CONTROL is null or c.CONTROL=1) and eq.PASSPORT = 1))

  order by eq.EQUIPMENT_PARENT_USER_ID, eq.NAME, eq.TYPE, eq.NUMBER, eq.NEMBER_INVENTORY

Этот запрос оказался НЕ верен, из него нужно убрать c.DISPLAY = 1 - а без этого параметра, в результате, появятся дубли оборудования
каждый сертификат имеет дату окончания, и статус актуализации CONTROL = 1 (1- серитификат актуален, 0 - сертификат не актуален)

Зарос работал до некоторого времени, пока пользователь выставлял c.DISPLAY = 1, т.е. отбражать один из сертификатов в окне оборудования. (проблема появилась, когда сертификаты можно скрывать - не выводить на экран, но по ним всеравно есть контроль (CONTROL = 1)) И это мне помогало избавится от отображения дублей оборудования, т.к. могут быть несколько просроченных сертификатов у одного оборудования.

Вопрос:
можно ли до операции JOIN в выше указанном запросе, выбрать из таблицы CERTIFICATE по одному просроченному сертификату для каждого оборудования, и обязательно вывести на экран, даже если DISPLAY = 0.
На рис. показано часть таблицы сертификатов относящиеся к двум оборудованием (с ID = 103 и 221) связка с табл. EQUIPMENT по полю DEVICE_ID_CL,
Т.е. я хочу делать по этой таблице группировку по полю DEVICE_ID_CL с таким расчетом, что результатом должна быть строка с ID=951 (в которой DISPLAY = 1), а если в группе только DISPLAY = 0, то вывести сертификат с максимальным ID, т.е. 835 исходя из рисунка
У меня группировка выходит только всегда с максимальным ID,

т.е. как выбрать строку с ID = 951 - не выходит

Заранее спасибо

[evg_m]

select top 1 max(id), device, display
from ...
group by device, display
order by display desc

[KBO]

Код не очень подошел...
- я использую FireBird 3.0 и там "top" - нет, вместо него взял "first" (если я вас правильно понял)
- может я не правильно выразился, извиняюсь, но исходя из картинки результатом запроса должны быть строки с ID: 835 и 951 (максимум я и сам могу найти каждой группы) )

Разъяснение:
- в кажой группе "DEVICE_ID_CL", может существовать только одна строка с c."DISPLAY"=1 (остальные строки в группе с значением "0"), а могут быть все с "0" в группе (т.е. с каждой группы сертификатов принадлежащих одному оборудованию, на экран выводится только один сертификат)
- выбираем строки с значением c.CONTROL=1 - обязателен для каждой группы
- если в группе строка с c."DISPLAY"=1 и c.CONTROL=1 - выводим эту строку (она может быть не с максимальным ID в группе)
- если в группе c."DISPLAY"=0 (во всех строках группы), то выводим строку с c.CONTROL=1 - и с максимальным ID в группе

[evg_m]

(она может быть не с максимальным ID в группе)

max(id) --- считается для КАЖДОЙ группы независимо
, device, display

может существовать только одна строка с c."DISPLAY"=1

максимум для ОДНОЙ строки просто равен данной сроке

выбираем строки с значением c.CONTROL=1 - обязателен для каждой группы

where control =1

[KBO]

Сообщение от evg_m

max(id) --- считается для КАЖДОЙ группы независимо
, device, display

да, независимо, - оно считается только для тех(той) групп(ы), в которой c."DISPLAY"=0 (во всех строках каждой группы) (для всех остальных групп, это группы там где есть DISPLAY=1 и CONTROL=1 - берется просто ID)

Сообщение от evg_m

where control =1

ага

Сообщение от evg_m

максимум для ОДНОЙ строки просто равен данной сроке

да (у каждой строки, может быть 0 или 1)

P.S.
Я чуть рисунок дополнил..., но результат остаётся тем же, ID: 835 и 951

[evg_m]

select device, 
gr0.id, gr1.id, -- просто на всякий случай 
case when gr1.id is null then gr0.id else gr1.id end as mixid --и вычисляем нужное значение
from ( select max(id) as id, device
          from  ...
          where control =1 
                                   and display =0  --возможно из без этого
          group by device
        ) gr0
left join (select id, device
             from ...
             where control =1 and display =1
           ) gr1 on gr0.device =gr1.device

[KBO]

Спасибо!!! интересный алгоритм...

а я вчера думал про "merge" (как у вас, сначало выборка с DISPLAY = "0", потом с "1", а потом слить их по "DEVICE_ID_CL")

[KBO]

Единственное что, мне кажется тут нужно не join применять, т.к. группировка по DEVICE - их нельзя сравнинвать, потому что это разные группы DEVICE, в одном случае в группе DISPLAY=0, в другом в группе - DISPLAY=1 - просто не будет объединения... а будут исключения

Результаты этих запросов получается нужно сложить. Скорее всего union необходим, чтоб собрать все DEVICE

[evg_m]

чтоб собрать все DEVICE

          where control =1 
                                   and display =0  --возможно из без этого
          group by device  --мы УЖЕ получили все возможные device

left join ( ...   where control =1 and display =1) gr1 on gr0.device =gr1.device

ДОПОЛНИТЬ все строки данными о тех же device но c display =1 ( если возможно)

[KBO]

Сейчас буду разбираться.

Я ошибся с union