MySQL 数据透视表和 JOINS

Question

我有一个包含三个表的 mysql 数据库

学生学生干预干预详情

我正在尝试创建一个数据透视 TableView ，该 View 显示所有学生，并且每个干预类型都有列，汇总了每个学生的不同干预类型。

到目前为止我有

 SELECT  t.`first_name`, t.`last_name`, t.`student_id`,
        Count(IF(t.`intervention_details_id` = 1, 1, null)) AS Intervention1,
        Count(IF(t.`intervention_details_id` = 0, 1, null)) AS Intervention2
 FROM  (
            SELECT student.`student_id`,  student.`first_name`,
                    student.`last_name`,
                    `student_intervention`.`intervention_details_id`
            FROM student, student_intervention 
            WHERE student_intervention.student_id = student.`student_id`
            ) t 
 GROUP BY t.student_id

这有效，但它只显示接受干预的学生的数据。我想要一份完整的学生名单，包括那些没有干预的学生。我想我需要一个 JOIN，但找不到合适的 JOIN。

有人能帮忙吗？

Answer 1

改用LEFT JOIN

SELECT  a.`student_id`,  
        a.`first_name`,
        a.`last_name`,
        SUM(IF(COALESCE(b.`intervention_details_id`,0) = 1, 1, 0)) Intervention1,
        SUM(IF(COALESCE(b.`intervention_details_id`,0) = 0, 1, 0)) Intervention2
FROM    student a
        LEFT JOIN student_intervention b
            ON b.student_id = a.`student_id`
GROUP BY a.`student_id`, a.`first_name`, a.`last_name`

如果你想要准备好的语句

SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN COALESCE(b.intervention_details_id ,0) = ',
      COALESCE(b.intervention_details_id ,0),
      ' THEN 1 ELSE 0 END) AS ',
      COALESCE(b.intervention_details_id ,0)
    )
  ) INTO @sql
FROM    student a 
        LEFT JOIN student_intervention b
            ON b.student_id = a.student_id;

SET @sql = CONCAT('SELECT  a.student_id , a.first_name , a.last_name , ', @sql, ' 
                   FROM     student a
                            LEFT JOIN student_intervention b
                                ON b.student_id = a.student_id 
                    GROUP BY a.student_id , a.first_name , a.last_name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;