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上的另一个表上，并获得最大(状态)值。

然而，我不关心超过900的状态，例如sts_id 1将给我999，尽管我更希望它给我100。

如果有一个可用于单个ID的值，我是否可以合并某种类型的案例来获得下一个值？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

Answer 2

我会使用apply和max()，但如下所示：

SELECT sts_id, v.MaxStatus
FROM TEST CROSS APPLY
     (SELECT MAX(status) as MaxStatus
      FROM (VALUES (status1), 
                   (status2), 
                   (status3), 
                   (status4)
           ) v(status)
      WHERE STATUS <= 900
     ) v;

这看起来可能与Aaron的答案基本相同。然而，有一个根本的区别。聚合发生在一行中，最多有4个值。这应该是非常快的。将聚合放在apply之外意味着需要聚合整个数据--当您想要添加列时，这会有点麻烦。

此外，这将返回所有行，即使没有状态满足这些条件。如果您希望在这种情况下过滤一行，则将where maxstatus is not null添加到外部查询。

Answer 3

也许是这样的？(将最终表输出引用为inline_view)：

        SELECT sts_id, MAX(status)
        FROM
        (
        SELECT sts_id, status, CASE WHEN status > 900 THEN 0 ELSE 1 END AS status_above_900
        FROM inline_view
        ) a1
        WHERE status_above_900 = 1
        GROUP BY sts_id