SQL Server - 案例

Question

CREATE TABLE test
(
sts_id int 
, [status1] int
, [status2] int
, [status3] int
, [status4] int
)
INSERT INTO test values
('1','999','0','0','0'),
('1','100','0','0','0'),
('2','200','999','0','0'),
('3','500','600','999','0'),
('4','200','700','900','998'),
('4','300','400','800','999')

在 SQL Server (2016) 中，我对上表进行查询以获得最高值 <= 900(如果可能)

SELECT DISTINCT 
   sts_id
 , CASE
       WHEN status1 > 0 AND status2 = 0 THEN status1
       WHEN status2 > 900 AND status3 = 0 THEN status1
       WHEN status2 BETWEEN 1 AND 900 AND status3 = 0 THEN status2
       WHEN status3 > 900 AND status4 = 0 THEN status2
       WHEN status3 BETWEEN 1 AND 900 AND status4 = 0 THEN status3
       WHEN status4 > 900 THEN status3 WHEN status4 BETWEEN 1 AND 900 THEN status4
       ELSE status1
   END AS 'status'
FROM test

给我这个:

| sts_ID | status |
|--------|--------|
|  1     |  100   |
|  1     |  999   |
|  2     |  200   |
|  3     |  600   |
|  4     |  800   |
|  4     |  900   |

然后我将它加入到 sts_id 上的另一个表中，并获取 max(status) 值。

但是，我不关心高于 900 的状态，例如 sts_id 1 会给我 999，即使我希望它给我 100。

如果有一个可用于单个 ID 的值，我有什么办法可以合并某种 CASE 来获得下一个值？ dense_rank() 可能有帮助吗？

Answer 1

如果您将表格反透视到单个 status 列，这似乎会更容易。这是一个带有 CROSS APPLY 的方法:

SELECT sts_id, MAX(status) MaxStatus
FROM TEST
CROSS APPLY (VALUES (status1), 
                    (status2), 
                    (status3), 
                    (status4)
            ) A (status)
WHERE STATUS <= 900
GROUP BY sts_id

结果:

STS_ID  MaxStatus
1       100
2       200
3       600
4       900