SQL计数不存在项 [英] SQL Count non existing item

问题描述

我有表组，

ID  NUMBER
STATUS  VARCHAR2(20 BYTE)

我可以计算状态数如下。

I am able to count the number of status as following.

select g.status, count(*) from groups g group by g.status;

STATUS                 COUNT(*)
-------------------- ----------
OK                            2 
NOK                           1 

我有另一个状态，说PENDING，REJECTED。但是表中没有项目，但我希望它们以零计数显示如下。

I have another status ,say PENDING, REJECTED. But there is no item exists in table, but I want them to be shown with zero count as following.

STATUS                 COUNT(*)
-------------------- ----------
OK                            2 
NOK                           1 
PENDING                       0 
REJECTED                      0 

会是怎样的SQL语句，使之可能？

What will be the SQL statement to make it possible?

推荐答案

试试这个

SELECT A.status, COUNT(DISTINCT G.ID) statusCnt
FROM (SELECT 'OK' status FROM DUAL
      UNION 
      SELECT 'NOK' status FROM DUAL
      UNION 
      SELECT 'PENDING' status FROM DUAL
      UNION 
      SELECT 'REJECTED' status FROM DUAL
     ) AS A 
LEFT JOIN groups G ON A.status = G.STATUS 
GROUP BY A.status;

这篇关于SQL计数不存在项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！