Блог

Я работаю с таблицей «Маршрут», в которой подробно описывается упорядоченный набор «Этапов» для конкретного ProductID:

 | ID | ProductID | StageID | Order |
|---- ----------- --------- -------|
|  0 |         1 |       4 |     1 |
|  1 |         1 |       2 |     2 |
|  2 |         1 |       3 |     3 |
|  3 |         1 |       7 |     4 |
|  4 |         1 |       5 |     5 |
|---- ----------- --------- -------|
  

И таблицу записей, в которой подробно указаны результаты прохождения / сбоя с отметкой времени для серийного номера продукта на определенном этапе:

 | ID | RecordDate          | Serial Number | StageID | Result |
|---- --------------------- --------------- --------- --------|
|  0 | 2019-03-16 00:00:00 | G001          |       4 |      1 |
|  1 | 2019-03-16 00:01:00 | G001          |       2 |      1 |
|  2 | 2019-03-16 00:02:00 | G001          |       2 |      0 |
|---- --------------------- --------------- --------- --------|
  

Из приведенных выше гипотетических данных вы можете видеть, что серийный номер G001 прошел этап 4 и ПРОШЕЛ, затем этап 2 и ПРОШЕЛ, но затем он снова прошел этап 2 и ПОТЕРПЕЛ НЕУДАЧУ.

Теперь я хочу выбрать результаты из этой таблицы записей для определенного SerialNumber, где StageID существует в маршруте для этого продукта. Это просто — вот мой запрос:

 SELECT StageID, Result, RecordDate
FROM Records
WHERE StageID IN (SELECT StageID FROM Route WHERE ProductID = 1) AND SerialNumber = 'G001';
  

Для приведенных выше данных это дает мне:

 | StageID | Result | RecordDate          |
|--------- -------- ---------------------|
|       4 |      1 | 2019-03-16 00:00:00 |
|       2 |      1 | 2019-03-16 00:00:01 |
|       2 |      0 | 2019-03-16 00:00:02 |
|--------- -------- ---------------------|
  

Теперь сложная часть, где, к сожалению, мои знания SQL заканчиваются, заключается в том, что я хочу выполнить фильтрацию по идентификатору StageID, чтобы отображался только самый последний результат в таблице записей для этого идентификатора StageID и SerialNumber. Итак, в данном примере я хочу это:

 | StageID | Result | RecordDate          |
|--------- -------- ---------------------|
|       4 |      1 | 2019-03-16 00:00:00 |
|       2 |      0 | 2019-03-16 00:00:02 |
|--------- -------- ---------------------|
  

Фильтрация может быть выполнена по дате записи или, что еще проще, по Records.ID .

Но я не могу определить SQL, который позволил бы достичь этого, и я думаю, что это тот случай, когда я недостаточно знаю язык, чтобы сформулировать то, что я пытаюсь сделать.

Итак, мой вопрос: возможно ли отфильтровать эти результаты по самой последней записи для каждого StageID?

Я играл с параметрами GROUP BY безрезультатно.

Использовать функции окна:

 SELECT r.StageID, r.Result, r.RecordDate
FROM (SELECT r.*
             ROW_NUMBER() OVER (PARTITION BY r.StageID ORDER BY r.RecordDate DESC) as seqnum
       FROM Records r
     ) JOIN
     Route ro
     ON ro.StageID = r.StageID AND ro.ProductId = 1
WHERE r.SerialNumber = 'G001';
  

Или вы можете добавить

 SELECT r.StageID, r.Result, r.RecordDate
FROM Records r
WHERE r.StageID IN (SELECT ro.StageID
                    FROM Route ro
                    WHERE ro.ProductID = 1
                   ) AND
      r.SerialNumber = 'G001' AND
      r.RecordDate = (SELECT MAX(r2.RecordDate)
                      FROM records r2
                      WHERE r2.StageId = r.StageId
                     );
  

 WITH cte 
     AS (SELECT StageID, 
                Result, 
                RecordDate, 
                Row_number() 
                  OVER( 
                    partition BY StageID 
                    ORDER BY Record_Date DESC) AS rn 
         FROM   table) 
SELECT StageID, 
       Result, 
       RecordDate 
FROM   cte 
WHERE  rn = 1 
ORDER  BY StageID DESC