Group by不返回0值

Question

我的表包含pk_id、reviewer_id、rating。有四种类型的评级。

1-very good.
2-good.
3-bad.
4-very bad.

我想计算一下每个评审员给出了多少评分。意思是:如果id为200的Akee对不同的代码给出了2个很好，4个很好，3个不好和0个很差的评级。

我想要结果

  count--- rate

   2---------1

   4---------2

   3---------3

   0---------4

我的问题是

SELECT COUNT(RATE),RATE
  FROM CODE_REVIEW WHERE CODE_REVIEWER_ID= 200
 GROUP BY RATE;

它正在显示结果

  count--- rate

   2---------1

   4---------2

   3---------3

我想要显示第四行，它是4级0。怎么做呢？？

Answer 1

如果Rate不是另一个表中的主键，那么您需要定义自己的Rate列表，以便MySQL知道rate的排列是什么：

SELECT  Rates.Rate,
        COUNT(Code_Review.Rate) AS CountOfRate
FROM    (   SELECT 1 AS Rate UNION ALL
            SELECT 2 AS Rate UNION ALL
            SELECT 3 AS Rate UNION ALL
            SELECT 4
        ) AS Rates
        LEFT JOIN Code_Review
            ON Code_Review.Rate = Rates.Rate
            AND CODE_REVIEWER_ID = 200
GROUP BY Rates.Rate

Answer 2

假设您没有定义费率的单独表。

SElECT * from (
  SELECT distinct(m.rate), countrate from code_review m 
  LEFT JOIN
  (SELECT COUNT(rate) as countrate,rate FROM code_review 
     WHERE code_reviewer_id=200 GROUP BY rate) t 
  ON m.rate=t.rate) a

Answer 3

如果可以，您应该推送以列格式获取它，因为它很简单，如下所示：

SELECT
    SUM(rate = 1) AS 1,
    SUM(rate = 2) AS 2,
    SUM(rate = 3) AS 3,
    SUM(rate = 4) AS 4
FROM
    code_review
WHERE
    code_reviewer_id = 200

但是如果你真的需要一种行格式，你可以这样做：

SELECT
    a.rate,
    COUNT(b.rate) AS cnt
FROM
    (
        SELECT 1 AS rate UNION ALL
        SELECT 2 AS rate UNION ALL
        SELECT 3 AS rate UNION ALL
        SELECT 4 AS rate
    ) a
LEFT JOIN
    code_review b ON a.rate = b.rate AND code_reviewer_id = 200
GROUP BY
    a.rate