問題描述

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

問題解答

題圖是Chinase,按這個來。Course確定情況下：CASE實現(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實現(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不確定，使用動態(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：

你應該缺學習一下原理百度'mysql行列轉(zhuǎn)換'只學會這一個sql也沒什么用

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