oracle 第一个非空,sql – 是否有一个聚合函数可以返回组中的第一个非空值？

我可能会误解为什么ROW NUMBER不适合你.我没有Oracle,但我在SQL Server中测试了这个,我相信它提供了您请求的结果：

WITH soTable AS

(

SELECT 'a' AS Name, null AS YearOfBirth

UNION ALL SELECT 'a', 2001

UNION ALL SELECT 'a', 2002

UNION ALL SELECT 'b', 1990

UNION ALL SELECT 'b', null

UNION ALL SELECT 'b', 1994

UNION ALL SELECT 'b', 1981

UNION ALL SELECT 'c', null

UNION ALL SELECT 'c', 2009

UNION ALL SELECT 'c', 2001

)

, soTableNoNulls AS

(

SELECT so.Name, so.YearOfBirth, ROW_NUMBER() OVER (PARTITION BY so.Name ORDER BY so.Name ASC) AS RowNumber

FROM soTable AS so

WHERE so.YearOfBirth IS NOT NULL

)

SELECT nn.Name, nn.YearOfBirth

FROM soTableNoNulls AS nn

WHERE nn.RowNumber = 1