mysql 一对多联合group查询, 将多条记录合并成一条记录, mysql两表联合查询，一对多

 

 

方式1:

select cjc.uid,u.uname,cjc.数学,cjc.英语,cjc.语文,u.type from 
 (select uid,max(case subj when '数学' then score else 0 end) 数学,
 max(case subj when '语文' then score else 0 end) 语文,
 max(case subj when '英语' then score else 0 end) 英语 
 from cj
 group by uid) cjc
 left join
 t_user u
 on u.uid=cjc.uid

方式2:

select cjc.uid,u.uname,cjc.数学,cjc.英语,cjc.语文,u.type from 
 (select uid,max(case subj when '数学' then score else 0 end) 数学,
 max(case subj when '语文' then score else '' end) 语文,
 max(case subj when '英语' then score else '' end) 英语 
 from cj
 group by uid) cjc
 left join
 t_user u
 on u.uid=cjc.uid

方式3:

SELECT a.uid,a.uname,a.type,GROUP_CONCAT(b.subj,b.score) from t_user a LEFT JOIN cj b ON a.uid=b.uid GROUP BY a.uid

方式4:

select u.uid,u.uname,cjc.数学,cjc.英语,cjc.语文,u.type from t_user u left join
 (select uid,max(case subj when '数学' then score else '' end) 数学,
 max(case subj when '语文' then score else '' end) 语文,
 max(case subj when '英语' then score else '' end) 英语 from cj group by uid) cjc
 on u.uid=cjc.uid