Сравнение двух дат в разных таблицах SQL с критериями
Нажмите на изображение, чтобы увидеть структуру таблицы и проблему /images/b105dc26b95e a8332493a3a3ffddc581ceb5b0fc.png
SELECT B.ENTITYID,
B.BALANCEDATE,
B.BALANCE,
MIN(DATEDIFF(DAY,B.BALANCEDATE,C.STATUSDATE)) RECENT
FROM BALANCES B JOIN STATUS C ON B.ENTITYID = C.ENTITYID
GROUP BY B.ENTITYID, B.BALANCEDATE,B.BALANCE
HAVING B.ENTITYID =1
Я пробовал следующее, но не могу пойти дальше, так как у более вложенных выборок есть проблемы с доступом к подобным атрибутам:
3 ответа
Также в SQLServer2005+ вы можете использовать опцию с оператором APPLY().
Оператор APPLY позволяет объединять два табличных выражения. Правое табличное выражение обрабатывается каждый раз для каждой строки из левого табличного выражения. Окончательный набор результатов содержит все выбранные столбцы из левого табличного выражения, за которыми следуют все столбцы правого табличного выражения. OUTER APLLY для тех строк, для которых нет соответствующих совпадений в правильном табличном выражении, он содержит значения NULL в столбцах правого табличного выражения.
SELECT e.EntityName, b.BalanceDate AS Date, b.Balance, o.Status
FROM Entity e JOIN Balances b ON e.EntityID = b.EntityID
OUTER APPLY (
SELECT TOP 1 s.Status AS Status
FROM Status s
WHERE b.EntityID = s.EntityID
AND s.StatusDate < b.BalanceDate
ORDER BY s.StatusDate DESC
) o
WHERE e.EntityName = 'ABCD'
Для повышения производительности (принудительная операция INDEX SEEK) используйте эти индексы с предложением INCLUDE. Предложение INCLUDE добавляет данные на самом низком уровне / уровне листьев, а не в дереве индексов. Это делает индекс меньше, потому что он не является частью дерева
CREATE INDEX x ON Status(StatusDate) INCLUDE(EntityID, Status)
CREATE INDEX x ON Entity(EntityName) INCLUDE(EntityID)
CREATE INDEX x ON Balances(EntityID, BalanceDate, Balance)
Демо на SQLFiddle
Смотрите ответ @ljh, если вы работаете на SQL Server. Решение ниже работает на MySQL. Это немного грязно, так как MySQL не поддерживает CTE а также Window Function,
MySQL
SET @entity_name = 'ABCD';
SELECT b.*, d.Status
FROM Entity a
INNER JOIN Balances b
ON a.EntityID = b.EntityID
LEFT JOIN
(
SELECT a.EntityID,
a.StatusDate StartDate,
b.StatusDate + Interval -1 DAY EndDate,
a.Status
FROM
(
SELECT b.*, @r1 := @r1 + 1 AS Row_number
FROM `Entity` a
INNER JOIN Status b
ON a.EntityID = b.EntityID
CROSS JOIN (SELECT @r1 := 0) rowCount
WHERE a.EntityName = @entity_name
ORDER BY b.Status ASC
) a
LEFT JOIN
(
SELECT b.*, @r2 := @r2 + 1 AS Row_number
FROM `Entity` a
INNER JOIN Status b
ON a.EntityID = b.EntityID
CROSS JOIN (SELECT @r2 := 1) rowCount
WHERE a.EntityName = @entity_name
ORDER BY b.Status ASC
) b ON a.Row_number = b.Row_number
) d
ON b.BalanceDate BETWEEN d.StartDate AND d.EndDate
WHERE a.EntityName = @entity_name
Краткое описание
Так как, MySQL не поддерживает функцию Windowing, такую как ROW_NUMBER(), запрос ниже использует User Variable предоставить номер строки, похожий на ROW_NUMBER() для каждой записи, которая затем будет использоваться для присоединения к другому подзапросу.
SELECT b.*, @r1 := @r1 + 1 AS Row_number
FROM `Entity` a
INNER JOIN Status b
ON a.EntityID = b.EntityID
CROSS JOIN (SELECT @r1 := 0) rowCount
WHERE a.EntityName = @entity_name
ORDER BY b.Status ASC
ВЫХОД
╔══════════╦═════════════════════════════════╦════════╦════════════╗
║ ENTITYID ║ STATUSDATE ║ STATUS ║ ROW_NUMBER ║
╠══════════╬═════════════════════════════════╬════════╬════════════╣
║ 1 ║ May, 29 2010 00:00:00+0000 ║ A ║ 1 ║
║ 1 ║ April, 16 2010 00:00:00+0000 ║ B ║ 2 ║
║ 1 ║ April, 02 2010 00:00:00+0000 ║ C ║ 3 ║
║ 1 ║ February, 26 2010 00:00:00+0000 ║ D ║ 4 ║
╚══════════╩═════════════════════════════════╩════════╩════════════╝
Основная цель предоставления номера строки для записей заключается в том, что он будет использоваться для присоединения к другому подзапросу, чтобы мы могли получить StartDate а также EndDate для каждого Status, Это легко SQL Server 2012 потому что у него есть оконная функция с именем LAG()
╔══════════╦═════════════════════════════════╦══════════════════════════════╦════════╗
║ ENTITYID ║ STARTDATE ║ ENDDATE ║ STATUS ║
╠══════════╬═════════════════════════════════╬══════════════════════════════╬════════╣
║ 1 ║ May, 29 2010 00:00:00+0000 ║ (null) ║ A ║
║ 1 ║ April, 16 2010 00:00:00+0000 ║ May, 28 2010 00:00:00+0000 ║ B ║
║ 1 ║ April, 02 2010 00:00:00+0000 ║ April, 15 2010 00:00:00+0000 ║ C ║
║ 1 ║ February, 26 2010 00:00:00+0000 ║ April, 01 2010 00:00:00+0000 ║ D ║
╚══════════╩═════════════════════════════════╩══════════════════════════════╩════════╝
После того, как диапазон состояния был организован. Теперь это основа как статус LookUp для каждого Balances,
Окончательный результат
╔══════════╦═════════════════════════════════╦═════════╦════════╗
║ ENTITYID ║ BALANCEDATE ║ BALANCE ║ STATUS ║
╠══════════╬═════════════════════════════════╬═════════╬════════╣
║ 1 ║ May, 01 2010 00:00:00+0000 ║ 100 ║ B ║
║ 1 ║ April, 01 2010 00:00:00+0000 ║ 50 ║ D ║
║ 1 ║ March, 01 2010 00:00:00+0000 ║ 75 ║ D ║
║ 1 ║ February, 01 2010 00:00:00+0000 ║ 85 ║ (null) ║
╚══════════╩═════════════════════════════════╩═════════╩════════╝
SQL Server 2012
Вышеупомянутый запрос продемонстрирован в MySQL может быть легко преобразован в TSQL используя Common Table Expression и Window Function который использует LAG() (тольков SQL Server 2012)
WITH lookupTable
AS
(
SELECT EntityID,
StatusDate StartDate,
DATEADD(DAY, -1, LAG(StatusDate) OVER(PARTITION BY EntityID ORDER BY Status)) EndDate,
Status
FROM Status
)
SELECT b.*, d.Status
FROM Entity a
INNER JOIN Balances b
ON a.EntityID = b.EntityID
LEFT JOIN lookupTable d
ON b.BalanceDate BETWEEN d.StartDate AND d.EndDate AND
d.EntityID = a.EntityID
WHERE a.EntityName = 'ABCD'
ВЫХОД
╔══════════╦═════════════════════════════════╦═════════╦════════╗
║ ENTITYID ║ BALANCEDATE ║ BALANCE ║ STATUS ║
╠══════════╬═════════════════════════════════╬═════════╬════════╣
║ 1 ║ May, 01 2010 00:00:00+0000 ║ 100 ║ B ║
║ 1 ║ April, 01 2010 00:00:00+0000 ║ 50 ║ D ║
║ 1 ║ March, 01 2010 00:00:00+0000 ║ 75 ║ D ║
║ 1 ║ February, 01 2010 00:00:00+0000 ║ 85 ║ (null) ║
╚══════════╩═════════════════════════════════╩═════════╩════════╝
Использование SQL Server 2012 в качестве СУБД в качестве примера. Это первый запрос, который вам нужен.
В этом ответе использовался SQL CTE(Common Table Expression), который может не относиться к другой системе RDBMS.
SQL FIDDLE DEMO
Объяснение запроса:
1. Сначала объедините таблицы [Balances] и [Status], используйте BalanceDate > StatusDate, чтобы отфильтровать результат, вернуть все столбцы из обеих таблиц, потому что все это потребуется позже.
2. Соедините выходные данные из шага.1 с таблицей [Entity], используйте EntityName для фильтрации результатов, при этом оставьте все столбцы из 3 таблиц, разумеется, дублирующий EntityID не нужен.
3. Используйте CTE, чтобы сохранить соединение
4. Используйте CTE для сохранения ранга, примененного к выходу соединения
5. Используйте номер ранга, чтобы отфильтровать результат и порядок по BalanceDate
;with CTE_AfterJoin
as
(
select E.EntityID, E.EnityName, C.BalanceDate, C.Balance, C.StatusDate, C.status
from Entity E
left join (
select B.EntityID, B.BalanceDate, B.Balance,S.StatusDate, S.[Status]
from Balances B
left join [Status] S
on B.EntityID = S.EntityID and B.BalanceDate > S.StatusDate
) C
on E.EntityID = C.EntityID
where E.EnityName = 'ABCD'
),
CTE_afterRank
as
(
select EnityName, BalanceDate, Balance,
rank() over (partition by BalanceDate order by StatusDate desc) as Rn, Status
from CTE_AfterJoin
)
select EnityName, BalanceDate, Balance, Status
from CTE_afterRank
where Rn = 1
order by BalanceDate desc