SQL - 考題－阿尼

在網路上看到這個問題，寫起來還挺有趣的，所以記錄一下，避免老了腦袋不靈光，有興趣的朋友不如也來考考自己。

問題如下：

有一個 Table 記載著每位學生每科得的等第，等第由0到5

CREATE TABLE #Test (
    STID INT, Math INT, Social INT, 
    Nature INT, English INT, Chinese INT, 
    Music INT, Physic INT, Work INT,
)
go
INSERT INTO #Test 
VALUES
    (9001, 5, 3, 3, 1, 0, 4, 1, 2),
    (9002, 0, 2, 3, 5, 1, 4, 5, 1),
    (9003, 3, 3, 4, 4, 4, 4, 2, 4),
    (9004, 2, 4, 4, 3, 5, 2, 5, 4),
    (9005, 4, 3, 1, 2, 4, 2, 5, 1),
    (9006, 4, 3, 5, 3, 5, 2, 3, 5),
    (9007, 0, 4, 1, 5, 0, 2, 5, 2),
    (9008, 3, 5, 3, 1, 5, 4, 3, 5),
    (9009, 3, 5, 5, 4, 4, 1, 3, 4),
    (9010, 3, 4, 3, 2, 4, 0, 3, 0),
    (9011, 5, 1, 1, 4, 3, 0, 0, 3),
    (9012, 2, 2, 1, 3, 1, 1, 5, 2),
    (9013, 4, 1, 4, 4, 3, 0, 0, 4),
    (9014, 3, 3, 5, 0, 5, 2, 1, 1),
    (9015, 2, 4, 0, 5, 3, 1, 0, 1)
go

試計算各科每個等第的人數，如下表

Level Math Social Nature English Chinese Music Physic Work
0 2 0 1 1 2 3 3 1
1 0 2 4 1 2 3 1 4
2 3 1 0 2 0 5 1 2
3 4 3 2 3 3 0 4 1
4 3 3 1 3 3 1 0 2
5 1 2 3 2 3 0 2 2

以下是我的三腳貓解法

CREATE TABLE #list(Seq INT)
INSERT INTO #list VALUES(0),(1),(2),(3),(4),(5)
go 
SELECT  Seq AS [Level],
        SUM(CASE WHEN Math = Seq THEN 1 ELSE 0 END) AS Math, 
        SUM(CASE WHEN Social = Seq THEN 1 ELSE 0 END) AS Social,
        SUM(CASE WHEN Nature = Seq THEN 1 ELSE 0 END) AS Nature,
        SUM(CASE WHEN English = Seq THEN 1 ELSE 0 END) AS English,
        SUM(CASE WHEN Chinese = Seq THEN 1 ELSE 0 END) AS Chinese,
        SUM(CASE WHEN Music = Seq THEN 1 ELSE 0 END) AS Music,
        SUM(CASE WHEN Physic = Seq THEN 1 ELSE 0 END) AS Physic,
        SUM(CASE WHEN Work = Seq THEN 1 ELSE 0 END) AS Work
FROM #Test test
CROSS JOIN #list 
GROUP BY Seq

題外話，這個貼程式碼的 plugin 實在太正點了！

[2010/4/21] Hunterpo帥氣的露了一手高招，程式碼如下：

CREATE TABLE #t 
(
    STID INT NOT NULL, 
    Math INT NOT NULL, 
    Social INT NOT NULL, 
    Nature INT NOT NULL, 
    English INT NOT NULL, 
    Chinese INT NOT NULL, 
    Music INT NOT NULL, 
    Physic INT NOT NULL, 
    Work INT NOT NULL
)
GO

INSERT INTO #t VALUES(9001, 5, 3, 3, 1, 0, 4, 1, 2);
INSERT INTO #t VALUES(9002, 0, 2, 3, 5, 1, 4, 5, 1);
INSERT INTO #t VALUES(9003, 3, 3, 4, 4, 4, 4, 2, 4);
INSERT INTO #t VALUES(9004, 2, 4, 4, 3, 5, 2, 5, 4);
INSERT INTO #t VALUES(9005, 4, 3, 1, 2, 4, 2, 5, 1);
INSERT INTO #t VALUES(9006, 4, 3, 5, 3, 5, 2, 3, 5);
INSERT INTO #t VALUES(9007, 0, 4, 1, 5, 0, 2, 5, 2);
INSERT INTO #t VALUES(9008, 3, 5, 3, 1, 5, 4, 3, 5);
INSERT INTO #t VALUES(9009, 3, 5, 5, 4, 4, 1, 3, 4);
INSERT INTO #t VALUES(9010, 3, 4, 3, 2, 4, 0, 3, 0);
INSERT INTO #t VALUES(9011, 5, 1, 1, 4, 3, 0, 0, 3);
INSERT INTO #t VALUES(9012, 2, 2, 1, 3, 1, 1, 5, 2);
INSERT INTO #t VALUES(9013, 4, 1, 4, 4, 3, 0, 0, 4);
INSERT INTO #t VALUES(9014, 3, 3, 5, 0, 5, 2, 1, 1);
INSERT INTO #t VALUES(9015, 2, 4, 0, 5, 3, 1, 0, 1);
GO

-- 利用 CTE
WITH unpvt AS
(
    -- 反轉置
    SELECT STID, [Subject], [Level]
    FROM (
        SELECT STID, Math, Social, Nature, English, Chinese, Music, Physic, Work
        FROM #t
    ) AS t
    UNPIVOT (
        [Level] FOR [Subject] IN 
        (Math, Social, Nature, English, Chinese, Music, Physic, Work)
    ) AS unpvt
), pvt AS (
    -- 轉置
    SELECT [Level], Math, Social, Nature, English, Chinese, Music, Physic, Work
    FROM (
        -- 彙總(計算人數)
        SELECT [Subject], [Level], COUNT(STID) AS Counter
        FROM unpvt
        GROUP BY [Subject], [Level]
    ) AS src
    PIVOT
    (
        SUM(Counter) FOR [Subject] IN
        (Math, Social, Nature, English, Chinese, Music, Physic, Work)
    ) AS pvt
)
SELECT *
FROM pvt;
GO

DROP TABLE #t;
GO