Запрос Sql с нетривиальным соединением

У меня есть две таблицы, которые я пытаюсь присоединиться к запросу, но мне трудно получить требуемый результат.

Таблица: Сканирование

ScanId ScanTime 1 8:00 2 8:15 3 9:00 4 9:30 6 10:00 7 10:45 8 11:00 9 11:10 

Таблица: Ответы

 ScanId RespA RespB 3 ABC X 7 DEF Y 9 GHI Z 

Есть внешний ключ для Responses.ScanId, который ссылается на Scans.ScanId.

Таблица ответов может содержать или не иметь соответствующую строку для каждой строки в таблице «Сканирование».

Мне нужно сделать вывод, показанный ниже, каждая строка в таблице «Сканы» возвращается с последними предыдущими значениями ответа.

Для Scans.ScanId 1 и 2 предыдущего ответа нет, поэтому столбцы «Ответ» равны нулю.

Для Scans.ScanId 3, 4 и 6, последний предыдущий RespA – ABC, а RespB – X (ScanId = 3)

Для Scans.ScanId 7 и 8 самым последним предыдущим RespA является DEF, а RespB – Y (ScanId = 7)

Для Scan.ScanId 9 самым последним предыдущим RespA является GHI, а RespB – Z (ScanId = 9)

Желаемый результат:

 ScanId ScanTime RespScanId RespA RespB 1 8:00 NULL NULL NULL 2 8:15 NULL NULL NULL 3 9:00 3 ABC X 4 9:30 3 ABC X 6 10:00 3 ABC X 7 10:45 7 DEF Y 8 11:00 7 DEF Y 9 11:10 9 GHI Z 

Мне трудно понять, как написать предложение соединения для этого. Он должен запускаться на Sql Server 2005 и выше.

Вот решение, использующее CTE …

 DECLARE @Scans TABLE ( ScanID INT, ScanTime DATETIME ) DECLARE @Responses TABLE ( ScanID INT, RespA VARCHAR(50), RespB VARCHAR(50) ) INSERT INTO @Scans VALUES (1, '8:00') INSERT INTO @Scans VALUES (2, '8:15') INSERT INTO @Scans VALUES (3, '9:00') INSERT INTO @Scans VALUES (4, '9:30') INSERT INTO @Scans VALUES (6, '10:00') INSERT INTO @Scans VALUES (7, '10:45') INSERT INTO @Scans VALUES (8, '11:00') INSERT INTO @Scans VALUES (9, '11:10') INSERT INTO @Responses VALUES (3, 'ABC', 'X') INSERT INTO @Responses VALUES (7, 'DEF', 'Y') INSERT INTO @Responses VALUES (9, 'GHI', 'Z') ;WITH ResponsesScan AS ( SELECT r.*, s.ScanTime FROM @Responses r JOIN @Scans s ON s.ScanID = r.ScanID ) SELECT s.ScanID, s.ScanTime, rs.ScanID AS RespScanId, rs.RespA, rs.RespB FROM @Scans s LEFT JOIN ResponsesScan rs ON rs.ScanID = ( SELECT TOP 1 ScanID FROM ResponsesScan WHERE ScanTime <= s.ScanTime ORDER BY ScanTime DESC ) 

С помощью функции окна:

 SELECT S.ScanId, ScanTime, R.ScanId AS RespScanId, RespA, RespB FROM Scans S LEFT OUTER JOIN (SELECT ScanId, RespA, RespB, LEAD(ScanId) OVER(ORDER BY ScanId) AS NextScanId FROM Responses) R ON S.ScanId >= R.ScanId AND (S.ScanId < R.NextScanId OR R.NextScanID IS NULL); 

Что возвращает PostgreSQL в соответствии с вашим набором результатов:

  scanid | scantime | respscanid | respa | respb --------+----------+------------+-------+------- 1 | 08:00:00 | | | 2 | 08:15:00 | | | 3 | 09:00:00 | 3 | ABC | X 4 | 09:30:00 | 3 | ABC | X 6 | 10:00:00 | 3 | ABC | X 7 | 10:45:00 | 7 | DEF | y 8 | 11:00:00 | 7 | DEF | y 9 | 11:10:00 | 9 | GHI | Z 

Мой внутренний запрос получает идентификатор сканирования и минимальный идентификатор сканирования над ним. Если нет, то он имеет значение NULL в качестве значения «NextScan». Затем я присоединяюсь к сканированию. Я квалифицирую каждый из них так, чтобы идентификатор сканирования был, по крайней мере, одним из них, но МЕНЬШЕ, чем сканирование после него.

 SELECT scans.scanID, scans.scanTime, rsp3.ScanID, rsp3.RespA, rsp3.RespB from scans LEFT JOIN ( SELECT rsp.scanID, ( select MIN( rsp2.scanID ) from responses rsp2 where rsp2.scanID > rsp.ScanID ) as NextScan from responses rsp ) PQ on Scans.ScanID >= PQ.ScanID AND Scans.ScanID <= case when PQ.NextScan IS NULL then PQ.ScanID else PQ.NextScan -1 end LEFT JOIN Responses rsp3 on PQ.ScanID = rsp3.ScanID 

Создавая выражение общего таблиц Майкла Фредриксона, вот еще одна версия запроса выбора.

Хотя этот запрос, по общему признанию, более сложный, он имеет разные характеристики производительности (то есть он может работать лучше). Однако разница в производительности является тривиальной, если вы индексируете столбец ScanTime.

 select s.ScanId, s.ScanTime, rs.ScanId as RespScanId, rs.RespA, rs.RespB from Scans s left outer join ResponsesScan rs on -- either there is a response for this specific scan rs.ScanId = s.ScanId or ( -- or there is a response from a previous scan rs.ScanTime < s.ScanTime -- and there does not exist any other response and not exists ( select 1 from ResponsesScan rs2 where -- for this specific scan rs2.ScanId = s.ScanId or ( -- or from a previous scan rs2.ScanTime < s.ScanTime -- that is later than that response and rs2.ScanTime > rs.ScanTime ) ) ) 
 select s.scanid, s.scantime, xjoin.RespScanId, r.respa, r.respb from scans s left join -- begin trick (select si.scanid, max(ri.scanid) as RespScanId from scans si left join responses ri on si.scanid >= ri.scanid group by si.scanid) as xjoin -- end trick on s.scanid = xjoin.scanid left join responses r on xjoin.RespScanId = r.scanid 
Interesting Posts

Разделить интервалы чисел на группы

Выполнение хранимой процедуры с помощью Dapper не возвращает заголовки столбцов, если строки не присутствуют

Определите, имеет ли параметр SP значение по умолчанию в T-SQL

Размер файла sql-сервера не уменьшается, когда я удаляю свои записи

DotNetNuke, указанный пароль для учетной записи пользователя «sa» недействителен

SQL select * vs. выбор определенных столбцов

получение записей только за текущий месяц

Дублирование таблицы с использованием Microsoft SQL Server Mangement

Как читать текстовый файл, который был преобразован из файла .SQL?

Нужно ли использовать блок try..catch и явный откат в процедуре SQL Server?

SQL Server 2008 R2 Express «Группировка» по двум таблицам

Оптимизировать запрос поиска базы данных

Рекомендации по производительности для условных запросов (поисковые формы)

BackgroundWorker все еще блокирует IHttpHandler

Объем переменных диапазона в SQL Server

Давайте будем гением компьютера.