none
如何計算各科各等第的人數 RRS feed

  • 問題

  • 我有一個table叫做Score, 記載著每位學生每科得的等第,等第由0到5 例如
    Score:
    STID Math Social Nature English Chinese Music Physic Work
    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

    現在我想算各科每個等第的人數,如下表Grade
    Grade
    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

    請問我要如何對Score資料表下sql才能得到我要的結果呢
    2010年4月18日 下午 12:46

解答

  • 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
    CREATE TABLE #list(Seq INT)
    INSERT INTO #list VALUES(0),(1),(2),(3),(4),(5)
    go 
    SELECT Seq,
    		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
    
    go 
    DROP TABLE #Test
    DROP TABLE #list
    • 已提議為解答 hunterpo0323 2010年4月21日 上午 05:49
    • 已標示為解答 Willardryan 2010年4月23日 下午 09:43
    2010年4月18日 下午 01:29
  • 因為資料表設計的關係,要用 PIVOT 的話得先 UNPIVOT,測試範例如下:

    USE tempdb;
    GO
    
    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

    看似麻煩,但效能上還不錯,你可以自行對照看看。


    Hunterpo's IT Vision - http://www.dotblogs.com.tw/hunterpo/

    2010年4月21日 上午 03:42

所有回覆

  • 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
    CREATE TABLE #list(Seq INT)
    INSERT INTO #list VALUES(0),(1),(2),(3),(4),(5)
    go 
    SELECT Seq,
    		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
    
    go 
    DROP TABLE #Test
    DROP TABLE #list
    • 已提議為解答 hunterpo0323 2010年4月21日 上午 05:49
    • 已標示為解答 Willardryan 2010年4月23日 下午 09:43
    2010年4月18日 下午 01:29
  • 也可以試試 PIVOT (如果 DBMS 是 SQL Server 2005 以上的話)
    以下為簽名檔,請勿對號入座:
    初學不是問題,但用不正確的態度來問問題,那就是很大的問題。
    請不要藉新手之名行小白之實,否則只會讓更多無辜的新手得不到幫助而已。
    如果不知道什麼是小白,請參閱:何謂小白
    2010年4月18日 下午 02:00
    版主
  •  

    小弟用的是SQLSERVER 2008,也一直嚐試使用PIVOT 但一直沒有成功,這個案例好像不是一般的CROSE TABLE

    請問要如何使用PIVOT 解決這個問題呢?

    2010年4月18日 下午 08:16
  • 你的資料表不太適合用 PIVOT,PIVOT 適合的案例是明細轉彙總表使用的。
    如果你的欄位是 STID, COURSENAME, GRADE 的話,就可以先利用 GROUP BY 先將 GRADE 的彙總取出來,再利用 PIVOT 轉成你要的結果。

    但依你目前的表格 ... 用 cursor 來做吧。


    以下為簽名檔,請勿對號入座:
    初學不是問題,但用不正確的態度來問問題,那就是很大的問題。
    請不要藉新手之名行小白之實,否則只會讓更多無辜的新手得不到幫助而已。
    如果不知道什麼是小白,請參閱:何謂小白
    2010年4月19日 上午 12:59
    版主
  • 因為資料表設計的關係,要用 PIVOT 的話得先 UNPIVOT,測試範例如下:

    USE tempdb;
    GO
    
    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

    看似麻煩,但效能上還不錯,你可以自行對照看看。


    Hunterpo's IT Vision - http://www.dotblogs.com.tw/hunterpo/

    2010年4月21日 上午 03:42
  • 這樣效能比我那一串來得好,當初倒是沒想到先做UNPIVOT。佩服!

    2010年4月21日 上午 04:16