数据库表的行列转置,
列转行
user_name | course | score |
---|---|---|
李四 | 数学 | 65 |
李四 | 英语 | 75 |
… | … | … |
SELECT user_name,'数学' as COURSE ,math_SCORE FROM stu_grade
UNION ALL
SELECT user_name,'语文' as COURSE,CN_SCORE FROM stu_grade
UNION ALL
SELECT user_name,'英语' as COURSE,eN_SCORE FROM stu_grade
ORDER BY user_name
行转列
现在有三个表
– student:(FID 学生号,FName 姓名),
– subject:(FSubID 课程号,FSubName 课程名),
– Score(FScoreId 成绩记录号, FSubID 课程号, FStdID 学生号, FScore 成绩)
– 怎么能实现这个表:
姓名 | 英语 | 数学 | 语文 | 历史 |
---|---|---|---|---|
张萨 | 78 | 67 | 89 | 76 |
王强 | 89 | 67 | 84 | 96 |
SELECT
st.fname,
SUM(CASE WHEN su.fsubname ='英语' THEN scy.fscore ELSE 0 END) AS '英语',
SUM(CASE WHEN su.fsubname ='数学' THEN scy.fscore ELSE 0 END) AS '数学',
SUM(CASE WHEN su.fsubname ='语文' THEN scy.fscore ELSE 0 END) AS '语文',
SUM(CASE WHEN su.fsubname ='历史' THEN scy.fscore ELSE 0 END) AS '历史'
FROM
Subject5 su,student5 st ,scouse5 scy
WHERE su.fsubid=scy.fsubid AND scy.fstdid=st.fid GROUP BY fname