MySQL行专列,多表联查行专列,sql行专列
1.表
1.学生表
#查询学生表所有内容
SELECT * FROM student;
主键id 姓名 年龄(出生日期) 性别
2.课程表
#查询课程表所有内容
SELECT * FROM course;
主键id 课程 教师
3.成绩表
#查询成绩表所有内容
SELECT * FROM sc;
学生id 课程id 成绩
4.普通查询结果集
#查询每个学生对应的科目的成绩
SELECT s.sname AS '姓名',sc.score AS '成绩',c.cname AS '科目'
FROM student s,course c,sc
WHERE s.sid = sc.sid AND c.cid = sc.cid
2. 方法一:CASE WHEN THEN
SELECT s.sname 姓名,
SUM(CASE c.cname WHEN '语文' THEN sc.score ELSE 0 END) 语文,
SUM(CASE sc.cid WHEN 2 THEN sc.score ELSE 0 END) 数学,
SUM(CASE c.cname WHEN '英语' THEN sc.score ELSE 0 END) 英语,
SUM(CASE sc.cid WHEN 4 THEN sc.score ELSE 0 END) 物理,
SUM(CASE c.cname WHEN '化学' THEN sc.score ELSE 0 END) 化学,
AVG(sc.score) 平均分,SUM(sc.score) 总分数
FROM student s,course c,sc
WHERE s.sid = sc.sid AND c.cid = sc.cid
GROUP BY sc.sid
结果:
3. 方法二:IF()函数
SELECT s.sname 姓名,
SUM(IF(sc.cid=1,sc.score,0)) 语文,
SUM(IF(c.cname='数学',sc.score,0)) 数学,
SUM(IF(sc.cid=3,sc.score,0)) 英语,
SUM(IF(c.cname='物理',sc.score,0)) 物理,
SUM(IF(sc.cid=5,sc.score,0)) 化学,
AVG(sc.score) 平均分,SUM(sc.score) 总分数
FROM student s,course c,sc
WHERE s.sid = sc.sid AND c.cid = sc.cid
GROUP BY sc.sid
结果:
4.方法三:子查询
SELECT s.sname 姓名,
SUM(CASE sc.cid WHEN (SELECT cid FROM course WHERE cname = '语文') THEN sc.score ELSE 0 END) 语文,
SUM(CASE sc.cid WHEN (SELECT cid FROM course WHERE cname = '数学') THEN sc.score ELSE 0 END) 数学,
SUM(CASE sc.cid WHEN (SELECT cid FROM course WHERE cname = '英语') THEN sc.score ELSE 0 END) 英语,
SUM(CASE sc.cid WHEN (SELECT cid FROM course WHERE cname = '物理') THEN sc.score ELSE 0 END) 物理,
SUM(CASE sc.cid WHEN (SELECT cid FROM course WHERE cname = '化学') THEN sc.score ELSE 0 END) 化学,
AVG(sc.score) 平均分,SUM(sc.score) 总分数
FROM student s,sc
WHERE s.sid = sc.sid
GROUP BY sc.sid
结果:
5.方法四:动态存储过程
SET @EE='';
SELECT @EE :=CONCAT(@EE,'sum(if(cid= ',cid,',score,0)) as ',cid, ',') AS aa FROM (SELECT DISTINCT cid FROM sc) A;
结果:
SET @QQ = CONCAT('select ifnull(sid) as userid,',@EE,' sum(score) as TOTAL FROM sc GROUP BY sid');
SELECT @QQ;
PREPARE stmt FROM @QQ;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
结果:
6.方法五:动态sql
SELECT s.sname 姓名,
(
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(c.cname = ''',
c.cname,
''', sc.score,0)) AS ''',
c.cname, ''''
)
)
FROM sc,course c
WHERE c.cid = sc.cid
) 需要执行的sql
FROM student s
结果:
将查询到的 ‘需要执行的sql’ 当成需要执行的sql语句执行后便可得到: