MySQL Group By If 子句

Question

嗨，我继承了一个使用 MySQL 作为数据库的学校出勤系统，其中管理员每周都会创建出勤记录(计划)，但在一周内记录可能会被更改或删除(实际)。

我需要显示两个表之间的所有不同记录(即实际与计划)。

理想情况下，我会在“实际”表中添加一个新列并跟踪所有已删除的记录，但不允许我更改架构。

我创建了一个MySQL SqlFiddle显示了我用来获取不同记录的架构和查询。

我的问题是我不明白如何将两行合并在一起并显示同一行中具有同一天值的记录。如果我按学生分组，那么它只显示第一条记录。

例如，如果“[tblPlanned]”中存在学生的记录，但“tblActual”中不存在，那么我需要显示它。我还需要显示 [tblActual] 中存在但 [tblPlanned] 中不存在的记录

我真正想要的是以某种方式添加一个子句(优先级)，如果学生的记录包含当天任一表中的值，则显示它，否则显示 null。

这是 SQL Fiddle SQL Fiddle显示数据结构和所需的输出

任何提示都会非常有帮助。

 SELECT * FROM (
             (SELECT a.Classroom as classroom, a.Student as student, 
      MAX(case (a.DropDate) when '20160222' then a.IsAbsent else ' ' end) as 'day_1',
      MAX(case (a.DropDate) when '20160223' then a.IsAbsent else ' '  end ) as 'day_2',
      MAX(case (a.DropDate) when '20160224' then a.IsAbsent else ' '  end ) as 'day_3'
FROM Attendance a
WHERE a.DropDate IN ('20160222','20160223','20160224') AND a.classroom = '17' AND  
      NOT EXISTS( SELECT 1 
                  FROM Staging AS p 
                  WHERE p.Student = a.Student AND
                        p.IsAbsent = a.IsAbsent AND
                        p.DropDate = a.DropDate
                )
             )
           UNION 
            (SELECT t.Classroom as classroom, t.Student as student,
      MAX(case (t.DropDate) when '20160222' then t.IsAbsent else ' '  end ) as 'day_1',
      MAX(case (t.DropDate) when '20160223' then t.IsAbsent else ' '  end ) as 'day_2',
      MAX(case (t.DropDate) when '20160224' then t.IsAbsent else ' '  end ) as 'day_3'
 FROM Staging t
 WHERE t.DropDate IN ('20160222','20160223','20160224') AND t.classroom = '17'AND  
       NOT EXISTS( SELECT 1
                   FROM Attendance AS u
                   WHERE u.Student = t.Student AND 
                         u.IsAbsent = t.IsAbsent AND 
                         u.DropDate = t.DropDate
                 )
            )
    ) tbl  ORDER BY  classroom, student

Answer 1

你在寻找这样的东西吗？ LEFT JOIN 或 RIGHT JOIN 子查询，具体取决于您希望哪个子查询具有优先级，然后使用 IFNULL 函数。

SELECT tbl2.classroom, tbl2.student, IFNULL(tbl2.day_1, tbl1.day_1) day_1
  ,IFNULL(tbl2.day_2, tbl1.day_2) day_2, IFNULL(tbl2.day_3, tbl1.day_3) day_3 
FROM 
((SELECT
  a.Classroom as classroom,
  a.Student as student,
  MAX( case (a.DropDate)
       when '20160222'
       then a.IsAbsent
       else ' '  end ) as 'day_1',
  MAX( case (a.DropDate)
       when '20160223'
       then a.IsAbsent
       else ' '  end ) as 'day_2',
  MAX( case (a.DropDate)
       when '20160224'
       then a.IsAbsent
       else ' '  end ) as 'day_3'
  FROM Attendance a
  WHERE a.DropDate IN ('20160222','20160223','20160224')
  AND a.classroom = '17'
  AND  NOT EXISTS 
      ( SELECT 1
        FROM Staging AS p
        WHERE p.Student = a.Student
          AND p.IsAbsent = a.IsAbsent
          AND p.DropDate = a.DropDate
      )
) as tbl1
RIGHT JOIN
(SELECT
  t.Classroom as classroom,
  t.Student as student,
  MAX( case (t.DropDate)
       when '20160222'
       then t.IsAbsent
       else ' '  end ) as 'day_1',
  MAX( case (t.DropDate)
       when '20160223'
       then t.IsAbsent
       else ' '  end ) as 'day_2',
  MAX( case (t.DropDate)
       when '20160224'
       then t.IsAbsent
       else ' '  end ) as 'day_3'
FROM Staging t
WHERE t.DropDate IN ('20160222','20160223','20160224')
  AND t.classroom = '17'
  AND  NOT EXISTS 
      ( SELECT 1
        FROM Attendance AS u
        WHERE u.Student = t.Student
          AND u.IsAbsent = t.IsAbsent
          AND u.DropDate = t.DropDate
      )
)tbl2 ON tbl1.classroom = tbl2.classroom and tbl1.student = tbl2.student) 


ORDER BY  classroom, student