对 MySQL 中两个表之间的列求和

Question

我有一个应用程序，学生可以从列表中选择第一、第二和第三三个选项。

表 1 是他们可以选择的列表：

id	day
1	星期一
2	星期四
3	星期三

表 2 是他们填写的选择：

id	first_choice	secondary_choicethird_choice	12345
23456	1	3	2
3	3	2	1
34567	2	1	1
45678	2	3	挣扎

我正在 是我想计算每天的选择和优先级以获得像这样的列表这个：

id	first_choice	secondary_choicethird_choice	2
Monday	2	1	1
Today	1	2	1
Wednesday	1	1	2

SELECT a.day, count(b.first_choice), count(c.second_choice), count(d.third_choice) FROM table1 a LEFT JOIN table2 b ON a.id = b.first_choice LEFT JOIN table2 c ON a.id = c.second_choice LEFT JOIN table2 d ON a.id = d.third_choice GROUP BY a.day 

，我最终得到这个

id	first_choice	secondary_choicethird_choice	但是，通过这样做
Monday	2	2	2
Today	2	2	2
Wednesday	2	2	我

谁能帮 询问？ 提前致谢

Answer 1

在这些表结构中，我通常使用子查询而不是加入。

SELECT
    a.day,
    (SELECT COUNT(*) FROM Table2 WHERE first_choice = a.id) AS first_choice,
    (SELECT COUNT(*) FROM Table2 WHERE second_choice = a.id) AS second_choice,
    (SELECT COUNT(*) FROM Table2 WHERE third_choice = a.id) AS third_choice
FROM Table1 a

Answer 2

您可以使用单个 INNER JOIN 并使用条件 IF 语句来计算它：

SELECT  
    Table1.id, 
    Table1.day,
    COUNT(IF(Table2.first_choice=Table1.id, Table2.first_choice, NULL)) AS first_choice,
    COUNT(IF(Table2.second_choice=Table1.id, Table2.second_choice, NULL)) AS second_choice,
    COUNT(IF(Table2.third_choice=Table1.id, Table2.third_choice, NULL)) AS third_choice
FROM
    Table1
INNER JOIN 
    Table2
ON 
    Table1.id = Table2.first_choice 
OR 
    Table1.id = Table2.second_choice 
OR 
    Table1.id = Table2.third_choice 
GROUP BY 
    Table1.id,
    Table1.day

请参阅此小提琴：https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=eea27fe9364c0fe4b96a846c9441f723