oracle 按可空列分组

Group By a nullable column

提问by Oguz Kurt

As you know Oracle treats NULLs as one "value" in GROUP BY queries. Is there a workaround solution to group data treating NULLs as different values each. For example:

如您所知，Oracle 将 NULL 视为 GROUP BY 查询中的一个“值”。是否有一种解决方法可以将数据分组，将 NULL 视为不同的值。例如：

table t:

药片：

colA     colB
 A         1 
 A         5 
<null>     3 
<null>     2

select colA, min(colB) from t group by colAreturns:

select colA, min(colB) from t group by colA返回：

colA     colB
 A         1 
<null>     2

but i want a query to return:

但我想要一个查询返回：

colA     colB
 A         1 
<null>     3 
<null>     2

Thanks in advance

提前致谢

回答by lc.

SELECT colA, MIN(colB)
FROM t
WHERE colA IS NOT NULL
GROUP BY colA
UNION ALL
SELECT colA, colB
FROM t
WHERE colA IS NULL

回答by A.B.Cade

@lc's answer is a great one, but just for the sport here is another solution:

@lc 的回答很好，但对于这项运动，这里有另一种解决方案：

SELECT  colA, min(colB)
FROM t
GROUP BY nvl(colA, rownum),colA

And hereis a sqlfiddle demo

而这里是一个sqlfiddle演示