mysql 给列求和_mysql 行转列，对列的分组求和，对行求和

CREATE TABLE students(

id INT PRIMARY KEY,

NAME VARCHAR(11)

);

CREATE TABLE courses(

id INT PRIMARY KEY,

NAME VARCHAR(11)

);

CREATE TABLE scores(

sid INT,

cid INT,

score INT,

RIMARY KEY (sid,cid)

);

3表关联查询：

SELECT st.id,st.name,c.NAME course,sc.score FROM students st

LEFT JOIN scores sc ON st.id=sc.sid

LEFT JOIN courses c ON sc.cid=c.id

将课程分类转为列名，其列值为分数：

SELECT t.id,t.name,

CASE course WHEN ‘语文‘ THEN score ELSE 0 END a,

CASE course WHEN ‘数学‘ THEN score ELSE 0 END b,

CASE course WHEN ‘英语‘ THEN score ELSE 0 END c,

CASE course WHEN ‘历史‘ THEN score ELSE 0 END d

FROM

(SELECT st.id,st.name,c.NAME course,sc.score FROM students st

LEFT JOIN scores sc ON st.id=sc.sid

LEFT JOIN courses c ON sc.cid=c.id) t

对上表按人员id分组，并将分完组的个列分数相加，组合到一行中，并对行求和：

SELECT t2.id,t2.name,SUM(a) ‘语文‘,SUM(b) ‘数学‘,SUM(c) ‘英语‘,SUM(d) ‘历史‘,SUM(a+b+c+d) ‘总分‘ FROM

(

SELECT t.id,t.name,

CASE course WHEN ‘语文‘ THEN score ELSE 0 END a,

CASE course WHEN ‘数学‘ THEN score ELSE 0 END b,

CASE course WHEN ‘英语‘ THEN score ELSE 0 END c,

CASE course WHEN ‘历史‘ THEN score ELSE 0 END d

FROM

(SELECT st.id,st.name,c.NAME course,sc.score FROM students st

LEFT JOIN scores sc ON st.id=sc.sid

LEFT JOIN courses c ON sc.cid=c.id) t

) t2

GROUP BY t2.id;