java - mysql查詢，這是怎麼查詢的呢

Question

#群組中看到別人發的一個面試題，=。 =，怎麼查詢的呢。

Answer 1

題圖是Chinase,按這個來。
Course確定情況下：
CASE實作：

SELECT
    `Name`,
    MAX(
        CASE
        WHEN Course = 'Chinase' THEN
            Score
        END
    ) AS Chinase,
    MAX(
        CASE
        WHEN Course = 'Math' THEN
            Score
        END
    ) AS Math,
    MAX(
        CASE
        WHEN Course = 'English' THEN
            Score
        END
    ) AS English
FROM
    table1
GROUP 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 English

FROM
    table1
GROUP 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;

Answer 2

雷雷

Answer 3

你應該缺少學習一下原理
百度"mysql行列轉換"
只學會這一個sql也沒什麼用

Answer 4

做資料統計的時候會用到這種神一樣的sql，平常phper工作上是用不上的。