mysql - 从两个不同的表中收集数据，Sql

Question

我有两个单独的表格，分别代表两周的学生出勤情况。我想生成两列，按周分割每个学生的出勤情况。如果学生每周出席多次，则应添加出席的次数。此外，如果学生在一周内出席，而下一周没有出席，则当月出席的学生将获得 1(假设仅出席一次)，而缺勤的学生将获得 0。我尝试了 count() 和 join 的多种变体，但没有成功。任何帮助将不胜感激。以下是截断的 fiddle :

http://www.sqlfiddle.com/#!9/36a6e0

这是我想要实现的目标的示例:

名称 |当前周|上周

宝拉 | 0 | 2

Answer 1

试试这个

2张 table

Select name, max(current_week) current_week, max(last_week) as last_week
    From (
    Select name, count(1) as current_week, 0 as last_week from currentWeek group by name
    union all
    Select name, 0 as current_week, count(1) as last_week from lastWeek group by name
    ) x
    group by name

3张 table

Select name, max(current_week) current_week, max(last_week) as last_week, max(third_week) as third_week
    From (
    Select name, count(1) as current_week, 0 as last_week, 0 as thrid_week from currentWeek group by name
    union all
    Select name, 0 as current_week, count(1) as last_week, 0 as thrid_week from lastWeek group by name
    union all
    Select name, 0 as current_week, 0 as last_week, count(1) as thrid_week from thirdWeek group by name
    ) x
    group by name