我的表有兩列：名稱和等級.看起來像這樣：

NAME | GRADE

Adam | 1

Adam | 2

Adam | 2

Adam | 3

Frank | 2

Frank | 1

現在,我想創建如下所示的視圖：

NAME | GRADE 1 | GRADE 2 | GRADE 3

Adam | 1 | 2 | 1

Frank | 1 | 1 | 0

我寫了這個：

SELECT Name,

(SELECT COUNT(Grade)

FROM dbo.Rodzaj

WHERE Grade = '1') as Grade_1,

(SELECT COUNT(Grade)

FROM dbo.Rodzaj

WHERE Grade = '2) as Grade_2,

(SELECT COUNT(Grade)

FROM dbo.Rodzaj

WHERE Grade = '3') as Grade_3

FROM dbo.Rodzaj

GROUP BY Name

但這行不通…

我將不勝感激任何幫助

解決方法:

您要查找的內容稱為“數據透視表”,它由一系列CASE語句完成,這些語句對每個條件應用1或0,然后對1和0進行SUM()檢索計數.

SELECT

NAME,

SUM(CASE WHEN GRADE = 1 THEN 1 ELSE 0 END) AS GRADE1,

SUM(CASE WHEN GRADE = 2 THEN 1 ELSE 0 END) AS GRADE2,

SUM(CASE WHEN GRADE = 3 THEN 1 ELSE 0 END) AS GRADE3

FROM Rodzaj

GROUP BY NAME

請注意,如果您需要具有動態的列數,則必須使用腳本語言和循環來構造查詢. (或存儲過程中的循環)

標簽：sql,mysql

來源： https://codeday.me/bug/20191201/2078623.html