我试图获取许多列中的第一个非空值。我知道我可以使用每列的子查询来完成此操作。以性能的名义(在这种情况下确实很重要),我想一次完成。
以以下示例数据为例:
col1 col2 col3 sortCol ==================================== NULL 4 8 1 1 NULL 0 2 5 7 NULL 3
我的梦想查询将在每个数据列中找到第一个非null值,并按排序sortCol。
sortCol
例如,选择第一三列的神奇骨料时,排序由所述sortCol下降。
col1 col2 col3 ======================== 5 7 0
或升序排序时:
col1 col2 col3 ======================== 1 4 8
有人知道解决方案吗?
first_value()
first_value(col)可以与一起使用and OVER (ORDER BY CASE WHEN col IS NOT NULL THEN sortcol ELSE maxvalue END)。ELSE maxvalue是必需的,因为SQL Server首先对null进行排序)
first_value(col)
and OVER (ORDER BY CASE WHEN col IS NOT NULL THEN sortcol ELSE maxvalue END)
ELSE maxvalue
CREATE TABLE foo(a int, b int, c int, sortCol int); INSERT INTO foo VALUES (null, 4, 8, 1), (1, null, 0, 2), (5, 7, null, 3);
现在,您可以看到我们必须执行什么操作来强制null在sortcol。之后排序。为此,desc您必须确保它们具有负值。
sortcol
desc
SELECT TOP(1) first_value(a) OVER (ORDER BY CASE WHEN a IS NOT NULL THEN sortcol ELSE 2^31-1 END) AS a, first_value(b) OVER (ORDER BY CASE WHEN b IS NOT NULL THEN sortcol ELSE 2^31-1 END) AS b, first_value(c) OVER (ORDER BY CASE WHEN c IS NOT NULL THEN sortcol ELSE 2^31-1 END) AS c FROM foo;
PostgreSQL稍微简单一些,
CREATE TABLE foo(a,b,c,sortCol) AS VALUES (null, 4, 8, 1), (1, null, 0, 2), (5, 7, null, 3); SELECT first_value(a) OVER (ORDER BY CASE WHEN a IS NOT NULL THEN sortcol END) AS a, first_value(b) OVER (ORDER BY CASE WHEN b IS NOT NULL THEN sortcol END) AS b, first_value(c) OVER (ORDER BY CASE WHEN c IS NOT NULL THEN sortcol END) AS c FROM foo FETCH FIRST ROW ONLY;
我相信,当RDBMS开始采用时,所有这些都将消失IGNORE NULLS。那只会是first_value(a IGNORE NULLS)。
IGNORE NULLS
first_value(a IGNORE NULLS)