java - mysql查询，这是怎么查询的呢

问题描述

群中看到别人发的一个面试题，=。=，怎么查询的呢。

问题解答

题图是Chinase,按这个来。Course确定情况下：CASE实现：

SELECT `Name`, MAX(CASEWHEN Course = ’Chinase’ THEN ScoreEND ) AS Chinase, MAX(CASEWHEN Course = ’Math’ THEN ScoreEND ) AS Math, MAX(CASEWHEN Course = ’English’ THEN ScoreEND ) AS EnglishFROM table1GROUP BY `Name`

IF实现：

SELECT `Name`,SUM(IF (Course = ’Chinase’, Score, 0)) AS Chinase,SUM(IF (Course = ’Math’, Score, 0)) AS Math,SUM(IF (Course = ’English’, Score, 0)) AS EnglishFROM table1GROUP BY `Name`

Course不确定，使用动态：

SET @CC=’’;SELECT @CC:=CONCAT(@CC,’SUM(IF(Course=’’,Course,’’’,’,Score,0)) AS ’,Course,’,’) FROM (SELECT DISTINCT Course FROM table1) A;SET @HH=CONCAT(’SELECT Name,’,LEFT(@CC,LENGTH(@CC)-1),’ FROM table1 GROUP BY Name’);PREPARE stmt FROM @HH;EXECUTE stmt;回答2：

select name, sum(case when Course=’Chinese’ then Score end) as Chinese,sum(case when type=’Math’ then Score end) as Math,sum(case when type=’English’ then Score end) as Englishfrom table1group by name回答3：

你应该缺学习一下原理百度'mysql行列转换'只学会这一个sql也没什么用

做数据统计的时候会用到这种神一样的sql，平时phper工作上是用不上的。