示意表
redid name subject score
aaa1 张三 数学 39
aaa2 张三 语文 89
aaa3 李四 数学 99
aaa4 李四 语文 88希望得到以下格式输出:
name shuxuicore shuxueRecId yuwenscore yuwenRecid
张三 39 aaa1 89 aaa2
李四 99 aaa3 88 aaa4刚才的帖子与我实际用的,有点不符,我修改了一下,
关键点是 “要展示每条成绩的记录id” 再次求帮助;
redid name subject score
aaa1 张三 数学 39
aaa2 张三 语文 89
aaa3 李四 数学 99
aaa4 李四 语文 88希望得到以下格式输出:
name shuxuicore shuxueRecId yuwenscore yuwenRecid
张三 39 aaa1 89 aaa2
李四 99 aaa3 88 aaa4刚才的帖子与我实际用的,有点不符,我修改了一下,
关键点是 “要展示每条成绩的记录id” 再次求帮助;
select name
,max(if(subject='数学',score,null)) as shuxuicore
,max(if(subject='数学',redid,null)) as shuxueRecId
,max(if(subject='语文',score,null)) as yuwenscore
,max(if(subject='语文',redid,null)) as yuwenRecid
from t1
group by name
demo2.`subject` as '科目2',demo2.score as '分数',demo2.redid as shuxueRecId FROM
demo demo1 LEFT JOIN demo demo2 ON demo1.name = demo2.name AND demo1.score <> demo2.score
GROUP BY demo1.`name`
我这种查询的先后顺序好像无法保证是数学先查出来,所有用科目字段代替了。楼主看看,能不能帮到你
用case when来实现行转列:SELECT name ,
MAX(CASE WHEN subject = '数学' THEN score
END) AS shuxuicore ,
MAX(CASE WHEN subject = '数学' THEN redid
END) AS shuxueRecId ,
MAX(CASE WHEN subject = '语文' THEN score
END) AS yuwenscore ,
MAX(CASE WHEN subject = '语文' THEN redid
END) AS yuwenRecid
FROM t1
GROUP BY name