mysql - SQL 中的条件和计数

Question

我有一个 MySQL 表 “收视率”，与

   an ID column
   a column called like_dislike (holds either null, 0, or 1),
   and a column called lesson_id (a foreign key from lessons). 

MySQL 表，“教训”，与

   an ID column
   a teacher_id column

我需要选择此信息:

   "SELECT r.like_dislike FROM ratings r INNER JOIN lessons l on l.lesson_id = r.lesson_id";

然而，这实际上是一个更大的 SQL 语句的一部分，我想做的是:

   Foreach lesson_id, if like_dislike == 0, SELECT count(like_dislike) as like

并且

   Foreach lesson_id, if like_dislike == 1, SELECT count(like_dislike) as dislike

我不知道如何将这个伪代码转换成SQL。我还需要在 SQL 中执行此操作，而不是在 PHP 等中执行此操作，因为它是较大 SQL 语句的一部分，而将其转换为格式正确的数组非常麻烦。

Answer 1

您应该能够通过分组来完成此任务。例如:

SELECT r.lesson_id, COUNT(*) AS like
FROM ratings r
INNER JOIN lessons l ON l.lesson_id = r.lesson_id
WHERE r.like_dislike = 0
GROUP BY r.lesson_id;

不喜欢也一样，只需将 WHERE 子句更改为

WHERE r.like_dislike = 1

编辑:

可以根据要求通过添加另一级别的分组将其合并到一个查询中:

SELECT r.lesson_id, r.like_dislike, COUNT(*) AS count
FROM ratings r
INNER JOIN lessons l ON l.lesson_id = r.lesson_id
GROUP BY r.lesson_id, r.like_dislike;

This will give you output, for example:    +-----------+--------------+-------+    | lesson_id | like_dislike | count |    +-----------+--------------+-------+    |         1 |            0 |    12 |    |         1 |            1 |     7 |    |         2 |            0 |     1 |    |         2 |            1 |     4 |    +-----------+--------------+-------+

so for lesson_id of 1, there are 12 likes, and 7 dislikes, etc...

EDIT 2:

To get one row for each lesson_id, you can modify the statement a little:

SELECT r.lesson_id,
  CASE WHEN r.like_dislike = 0 THEN COUNT(*) END AS like,
  CASE WHEN r.like_dislike = 1 THEN COUNT(*) END AS dislike
FROM ratings r
INNER JOIN lessons l ON l.lesson_id = r.lesson_id
GROUP BY r.lesson_id, r.like_dislike;

就此而言，您甚至根本不需要加入类(class)表，除非您以某种方式获得与类(class)无关的评分。如果您想包含没有评分的类(class)，则必须更改为 OUTER 联接:

SELECT l.lesson_id,
  CASE WHEN r.like_dislike = 0 THEN COUNT(*) END AS like,
  CASE WHEN r.like_dislike = 1 THEN COUNT(*) END AS dislike
FROM lessons l
LEFT JOIN ratings r ON r.lesson_id = l.lesson_id
GROUP BY l.lesson_id, r.like_dislike;