SQL(其中)返回没有结果

Question

oracle" table1"

number code
111    aaa
222    bbb
333    aaa    

查询

SELECT COUNT(*) AS CNT, CODE
FROM TABLE1
WHERE NUMBER IN (
'444',
'111',
'333',
'222'
) GROUP BY CODE;

答案

CNT CODE
2   aaa
1   bbb

需要结果，请帮助。

CNT  CODE
1    null
2    aaa
1    bbb

我尝试使用" nvl"请求，但它仅返回（计数）行（null）代码，但不计数行。

ps。对不起，英语不好：）

Answer 1

我想你想要一个 left join：

SELECT CODE, COUNT(t1.NUMBER) AS CNT
FROM (SELECT '444' as num FROM DUAL UNION ALL
      SELECT '111' as num FROM DUAL UNION ALL
      SELECT '333' as num FROM DUAL UNION ALL
      SELECT '222' as num FROM DUAL 
     ) x LEFT JOIN
     TABLE1 t1
     ON t1.NUMBER = x.num
GROUP BY CODE;