SQL行转列

来源：互联网发布：夜书所见的知什么意思编辑：程序博客网时间：2024/05/22 12:35

1.SQL行转列

1.1 test表结构

year, month, amout

1.2 test表数据

1.3 查询SQL语句

SELECTa.`year`,m1,m2,m3,m4FROM((SELECT`year`,amout AS m1FROMtestWHERE`month` = 1) aLEFT JOIN (SELECT`year`,amout AS m2FROMtestWHERE`month` = 2) b ON a.`year` = b.`year`LEFT JOIN (SELECT`year`,amout AS m3FROMtestWHERE`month` = 3) c ON a.`year` = c.`year`LEFT JOIN (SELECT`year`,amout AS m4FROMtestWHERE`month` = 4) d ON a.`year` = d.`year`)

1.4 查询结果

2.查询没门课都大于80分的学生

2.1 student表结构

name, course, marks

2.2 student表数据

2.3 查询SQL

SELECTb.`name`,b.course_nums,a.sc_course_numsFROM(SELECT`name`,COUNT(course) AS course_numsFROMstudentGROUP BY`name`) bLEFT JOIN (SELECT`name` AS sc_name,COUNT(course) AS sc_course_numsFROMstudentWHEREmarks >= 80GROUP BY`name`) a ON a.sc_name = b.`name`AND b.course_nums = a.sc_course_numsWHEREsc_course_nums IS NOT NULL

2.4 查询结果

1 0