php - 需要一些解决方案来解决我的 mysql 表在 2 个条件下的结果

Question

我有这样的 table

table_name : AnswerDetail

|id_session|id_question|value|
------------------------------
|1         |1          |4    |
|1         |2          |4    |
|1         |3          |4    |
|1         |4          |2    |
|1         |1          |3    |
|1         |2          |2    |
|1         |3          |1    |
|1         |4          |4    |
|2         |1          |3    |
|2         |2          |2    |
|2         |3          |2    |
|3         |1          |4    |

我需要在这里按 2 个条件显示一些结果，

$query = $this->db->query("SELECT a.id_session, a.id_question,
(SELECT COUNT(*) FROM AnswerDetail WHERE id_session=1 AND value=4 AND id_question=1) AS great,
(SELECT COUNT(*) FROM AnswerDetail WHERE id_session=1 AND value=3 AND id_question=1) AS good"),
(SELECT COUNT(*) FROM AnswerDetail WHERE id_session=1 AND value=2 AND id_question=1) AS not bad
FROM (SELECT DISTINCT id_session,id_question FROM AnswerDetail) a WHERE id_session=1);

我需要做的是，我把id_session通过 URL 段的 ID(没问题)，但是 id_question通过从 foreach php 循环的数组。

那是查询由 id_question=1 显示, 我怎样才能通过 id_question by [1,2,3,4] 得到结果

我所期望的是这样的

|id_session|id_question|great|good|not bad|
-------------------------------------------
|1         |1          |1    |1   |0      |
|1         |2          |1    |0   |1      |
|1         |3          |1    |0   |0      |
|1         |4          |1    |0   |1      |

有什么办法吗？或者我应该改变什么？

Answer 1

您可以使用 sum() 而不是 count() 然后按 session 和 question id 分组。

select id_session, id_question, sum(case when value = 4 then 1 else 0 end) as great,
    sum(case when value = 3 then 1 else 0 end) as good,
    sum(case when value = 2 then 1 else 0 end) as notbad
from AnswerDetail
where id_session = 1
group by id_session, id_question