代码之家 › 专栏 › 技术社区 › Q''

筛选单个列上的选择结果

sql

Q'' · 技术社区 · 7 年前

我正在使用一个“Route”表,该表详细说明了特定ProductID的一组有序“Stages”:

| 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阶段,但是它再次通过了第2阶段,并且失败了。

现在,我想从这个记录表中为一个特定的序列号选择结果,其中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 |
|---------+--------+---------------------|

结果显示在该StageID和SerialNumber的记录表中。在给定的例子中,我想要:

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

过滤可以在RecordDate上完成,或者更容易地在Records.ID上完成。

但是我不知道如何使用SQL来实现这一点,我认为这是因为我对这门语言了解不够,无法清楚地表达我正在尝试做的事情。

所以我的问题是:有没有可能在每个阶段的最新记录上过滤这些结果?

2 回复 | 直到 7 年前

Gordon Linoff 7 年前

使用窗口功能:

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
                     );

Ana Iuliana Tuhasu 7 年前

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