在分组结果集中，选择最新行.如果最新行中的特定字段为0，则选择下一行

Question

CREATE TABLE TableOne(Name VARCHAR(10), Val1 INT, Val2 INT, Val3 INT, RDate DATE)
INSERT TableOne VALUES ('Rec01', 0, 0, 0, '11-Nov-2017'), ('Rec02', 0, 20, 30, '10-Nov-2017'), ('Rec03', 0, 0, 0, '09-Nov-2017'), ('Rec01', 10, 20, 30, '11-Nov-2017'), ('Rec02', 10, 20 ,30, '10-Nov-2017'), ('Rec03', 0, 0, 30, '08-Nov-2017'), ('Rec01', 0, 20, 0, '10-Nov-2017'), ('Rec02', 10, 20, 0, '09-Nov-2017'), ('Rec03', 10, 0, 30, '08-Nov-2017')

tableone

 -------------------------------------------
|  Name  | Val1 | Val2 | Val3 |    RDate    |
|-------------------------------------------|
| Rec01  |   0  |   0  |   0  | 11-Nov-2017 |
| Rec02  |   0  |  20  |  30  | 10-Nov-2017 |
| Rec03  |   0  |   0  |   0  | 09-Nov-2017 |
| Rec01  |  10  |  20  |  30  | 11-Nov-2017 |
| Rec02  |  10  |  20  |  30  | 10-Nov-2017 |
| Rec03  |   0  |   0  |  30  | 08-Nov-2017 |
| Rec01  |   0  |  20  |   0  | 10-Nov-2017 |
| Rec02  |  10  |  20  |   0  | 09-Nov-2017 |
| Rec03  |  10  |   0  |  30  | 08-Nov-2017 |
 ------------------------------------------- 

这些记录必须按"名称"分组，并且需要在以下条件下选择每个组的最新记录

1. If a record has two rows with same date, select first row. 
2. If a record has two rows with same date but Val1, Val2, Val3 are 0 then select next row with same date

预期结果为

 -------------------------------------------
|  Name  | Val1 | Val2 | Val3 |    RDate    |
|-------------------------------------------|
| Rec01  |  10  |  20  |  30  | 11-Nov-2017 |
| Rec02  |   0  |  20  |  30  | 10-Nov-2017 |
| Rec03  |   0  |   0  |   0  | 09-Nov-2017 |
 ------------------------------------------- 

我尝试了row_number（）

的分区

SELECT Name, Val1, Val2, Val3, RDate FROM (
SELECT Name, Val1, Val2, Val3, Max(RDate) AS RDate, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY RDate DESC) AS Rno FROM TableOne GROUP BY Name, Val1, Val2, Val3, RDate) TempTable where TempTable.rno = 1

返回

 -------------------------------------------
|  Name  | Val1 | Val2 | Val3 |    RDate    |
|-------------------------------------------|
| Rec01  |   0  |   0  |   0  | 11-Nov-2017 |
| Rec02  |   0  |  20  |  30  | 10-Nov-2017 |
| Rec03  |   0  |   0  |   0  | 09-Nov-2017 |
 ------------------------------------------- 

对于rec01，而不是第一行，我如何选择与val1，val2和val3相同日期的下一行？

谢谢

Answer 1

尝试以下查询

SELECT  Name,Val1,Val2,Val3,RDate
FROM    (
    SELECT  *   
        ,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY RDATE DESC
            ,(CASE WHEN Val1 +Val2 +Val3 > 0 THEN 1 ELSE 0 END) DESC) AS SlNo
    FROM    TableOne
)   AS  T
WHERE   T.SlNo = 1

输出： -

Name    Val1    Val2    Val3    RDate
Rec01   10      20      30      2017-11-11
Rec02   0       20      30      2017-11-10
Rec03   0       0       0       2017-11-09

Answer 2

如果将Val的总和作为情况添加为第二子句：

SELECT Name, Val1, Val2, Val3, RDate FROM (
SELECT Name, Val1, Val2, Val3, Max(RDate) AS RDate, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY RDate DESC,val1+val2+val3 desc) AS Rno FROM TableOne GROUP BY Name, Val1, Val2, Val3, RDate) TempTable where TempTable.rno = 1