Блог

Я работаю над несколькими таблицами данных, пытаясь найти наилучший способ их объединения. Я использую основную таблицу (давайте назовем ее Devices ) вместе с дополнительными таблицами, Settings . Все упомянутые таблицы содержат DeviceID ‘s, которые я использую в качестве идентификатора устройства.

Дело в том, что когда в таблице есть отдельные записи устройств Devices , Settings таблица может содержать более одной записи на устройство.

Базовая структура таблицы устройств выглядит следующим образом:

 | deviceId | deviceName     |
| -------- | -------------- |
| 0001     | device01       |
| 0002     | device02       |
| 0003     | device03       |
 

Settings однако использует свой собственный идентификатор записей, таким образом, похоже, что это много случаев:

 | recordId | deviceId | settingState   |
| -------- | -------- | -------------- |
| 1        | 0001     | OK             |
| 2        | 0001     | OK             |
| 3        | 0001     | NOK            |
| 4        | 0002     | NOK            |
| 5        | 0002     | N/A            |
| 6        | 0003     | N/A            |
 

Чего я пытаюсь добиться, так это следующего: если на устройстве присутствует запись с состоянием настройки «OK» (среди всех других записей, соответствующих ‘DeviceID’), этот статус должен быть возвращен. Если на компьютере нет соответствующей записи «OK», тогда «NOK» считается следующим приоритетом, а «N / A» возвращается только тогда, когда не возвращаются записи о состоянии «OK» и «NOK».

Пример результата:

 | deviceId | deviceState    |
| -------- | -------------- |
| 0001     | OK             |
| 0002     | NOK            |
| 0003     | N/A            |
 

До сих пор мне удавалось без проблем с объединением на устройствах, когда есть отношение 1-1, однако я застрял на обработке более одной записи на устройство.

Я попытался с CASE помощью, но мне не удалось правильно сконструировать a JOIN для работы с DeviceId помощью отдельных SELECT операторов. Заранее спасибо!

Вы можете использовать row_number() :

 select s.*
from (select s.*,
             row_number() over (partition by deviceid
                                order by case settingState when 'OK' then 1 when 'NOK' then 2 when 'N/A' then 3 else 4 end
                               ) as seqnum
      from settings s
     ) s
where seqnum = 1;
 

Забавный метод использует агрегацию:

 select deviceid,
       coalesce(max(case when settingState = 'OK' then settingState end),
                max(case when settingState = 'NOK' then settingState end),
                max(case when settingState = 'N/A' then settingState end),
                max(settingState)
               ) as settingState
from settings s
group by deviceid;