MySQL将多行合并为一行

Question

我有两张桌子如下：

1. tbl_student：

id  name
1   raj
2   raja
3   raju
4   rajan

1. tbl_attendance id student_id月份attended_days 1 1 1 6 2 2 16 3 8 8 7 14 5 2 13 6 7 1 11

我需要将这两个表连接起来，将来自tbl_attendance的每个学生每月的多行合并成一行，以获得如下结果：

 id     name    month   attended_days   month   attended_days
  1     raj         1               6       2              16
  7     raja        1              11       2              14
  8     rajan       1               8       2              13

提前感谢您的帮助。

Answer 1

而不是在每个记录中显示月份值，

您可以将它们用作列标题，并将考勤用作它们的值。

使用枢轴式解决方案来实现所需的解决方案。

示例：

select s.id as student_id
     , s.name as student_name
     , max( case when a.month = 1 then a.attended_days else null end ) as month_1
     , max( case when a.month = 2 then a.attended_days else null end ) as month_2
     , max( case when a.month = 3 then a.attended_days else null end ) as month_3
--     ...
     , max( case when a.month = 12 then a.attended_days else null end ) as month_12
  from table_student s
  left join table_attendance a on s.id = a.student_id
 group by s.id, s.name

Answer 2

你的问题不是很完整，但我想你想要这样的东西：

 select s.*, 
    coalesce(a1.month, a2.month, a3.month) as month,
    coalesce(a1.attended_days , a2.attended_days , a3.attended_days ) as attended_days 
     from table_student s
      left join table_attendance a1 on s.id = a1.student_id and a1.month = 1
      left join table_attendance a2 on s.id = a2.student_id and a2.month = 2
      left join table_attendance a3 on s.id = a3.student_id and a3.month = 3

如果要在一列中显示所有月份，则使用前面的代码。对于多列，可以使用以下示例：

  select s.*, 
    a1.month as month_1, 
    a2.month as month_2,
    a3.month  as month_3,
    a1.attended_days as attended_days_1, 
    a2.attended_days as attended_days_2, 
    a3.attended_days as attended_days_3  
     from table_student s
      left join table_attendance a1 on s.id = a1.student_id and a1.month = 1
      left join table_attendance a2 on s.id = a2.student_id and a2.month = 2
      left join table_attendance a3 on s.id = a3.student_id and a3.month = 3

在这12个月里都这么做。我以3为例。