問(wèn)題描述

群中看到別人發(fā)的一個(gè)面試題，=。=，怎么查詢的呢。

問(wèn)題解答

題圖是Chinase,按這個(gè)來(lái)。Course確定情況下：CASE實(shí)現(xiàn)：

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實(shí)現(xiàn)：

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不確定，使用動(dòng)態(tài)：

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：

你應(yīng)該缺學(xué)習(xí)一下原理百度'mysql行列轉(zhuǎn)換'只學(xué)會(huì)這一個(gè)sql也沒什么用

做數(shù)據(jù)統(tǒng)計(jì)的時(shí)候會(huì)用到這種神一樣的sql，平時(shí)phper工作上是用不上的。