Блог

У меня есть две таблицы, «базовая» и «переопределенная», в базе данных SQLite, которые мне нужно объединить на базе.ИДЕНТИФИКАТОР и переопределение.Базовый идентификатор с минимальным значением (переопределить.Приоритет). Переопределение.BaseID может быть ‘0’, и в этом случае переопределение является «глобальным» и применяется ко всем базовым записям, если не существует переопределяющей записи с базовым идентификатором и имеет равное или меньшее переопределение.Значение приоритета. Также возможно вообще не иметь записи глобального переопределения, что означает, что я хочу вернуть базовую запись только в том случае, если у них есть соответствующее переопределение.Присутствует запись BaseID. Я могу сделать это в памяти или в хранимой процедуре, но есть ли способ сделать все это в одном запросе?

Например, вот некоторые «базовые» данные:

 ID | Name
----------
1  | Test1
2  | Test2
3  | Test3
  

И вот некоторые «переопределенные» данные:

 ID | BaseID | Priority | Authorized
-----------------------------------
1  | 1      | 1        | 0
2  | 3      | 1        | 1
3  | 0      | 2        | 1
  

И вот что я хочу, чтобы запрос возвращал:

 base.ID | base.Name | override.ID | override.Authorized
-------------------------------------------------------
1       | Test1     | 1           | 0
2       | Test2     | 3           | 1
3       | Test3     | 2           | 1
  

Если бы мы удалили переопределение.ID ‘3’ Я бы ожидал следующего:

 base.ID | base.Name | override.ID | override.Authorized
-------------------------------------------------------
1       | Test1     | 1           | 0
3       | Test3     | 2           | 1
  

Я придумал это:

Сначала найдите глобальное переопределение с наименьшим приоритетом (если оно существует) и присоедините его к базовому набору (что предотвращает возврат любых базовых строк, если нет глобального переопределения), и добавьте ‘.1’ к приоритету:

 SELECT *
FROM base
JOIN (SELECT ID AS OverrideID, BaseID, Authorized, MIN(Priority)   '.1' AS Priority
    FROM override
    WHERE BaseID = 0)
  

Затем найдите неглобальные переопределения с наименьшим приоритетом, сгруппированные по BaseID, внутреннее соединение с базовой таблицей на базе.ИДЕНТИФИКАТОР и добавьте ‘.0’ к приоритету:

 SELECT *
FROM base b
JOIN (SELECT ID AS OverrideID, BaseID, Authorized, MIN(Priority)   '.0' AS Priority
    FROM override
    WHERE BaseID != 0
    GROUP BY BaseID) o ON b.ID = o.BaseID
  

Наконец, ОБЪЕДИНИТЕ два набора данных и выберите значение наименьшего приоритета, сгруппированное по base.ID:

 SELECT *, MIN(Priority)
FROM (SELECT *
    FROM base
    JOIN (SELECT ID AS OverrideID, BaseID, Authorized, MIN(Priority)   '.1' AS Priority
        FROM override
        WHERE BaseID = 0)
    UNION
    SELECT *
    FROM base b
    JOIN (SELECT ID AS OverrideID, BaseID, Authorized, MIN(Priority)   '.0' AS Priority
        FROM override
        WHERE BaseID != 0
        GROUP BY BaseID) o ON b.ID = o.BaseID)
GROUP BY ID;
  

Объединенные десятичные точки действуют как тай-брейк в случае, если приоритет глобального и неглобального переопределения одинаков, предоставляя предпочтение неглобальному. Похоже, это работает до сих пор. Вероятно, его можно реорганизовать для большей эффективности, но он достаточно точен и быстр для моих целей.

Обновить:

Вау. Ну, мне стало немного скучно, и я решил провести рефакторинг. Я смог значительно упростить запрос:

 SELECT *, MIN(override.Priority   CASE WHEN override.BaseID = 0 THEN '.1' ELSE '.0' END)
FROM base
JOIN override ON (override.BaseID = 0 OR override.BaseID = base.ID)
GROUP BY base.ID;
  

Жаль, что мне не нужно было выполнять concat в функции MIN(), и если бы мне не нужно было беспокоиться о тай-брейках, мне бы это не понадобилось, но это кажется отличным решением.

Сначала получите строки с наименьшим значением приоритета для каждого базового идентификатора. (Для использования MIN() таким образом требуется SQLite 3.7.11 или более поздней версии.)

 SELECT ID,
       BaseID,
       MIN(Prority) AS Priority,
       Authorized
FROM override;
  

Затем отфильтруйте любую из тех строк, которые являются недопустимыми из-за переопределения gloval:

 ...
WHERE NOT EXISTS (SELECT 1
                  FROM override AS g
                  WHERE g.BaseID   = 0
                    AND g.Priority < override.Priority);
  

Затем это можно просто объединить:

 SELECT base.ID,
       base.Name,
       o.ID,
       o.Authorized
FROM base
JOIN (SELECT ID,
             BaseID,
             MIN(Prority) AS Priority,
             Authorized
      FROM override
      WHERE NOT EXISTS (SELECT 1
                        FROM override AS g
                        WHERE g.BaseID   = 0
                          AND g.Priority < override.Priority)
     ) AS o
     ON base.ID = o.BaseID OR o.BaseID = 0;