考題又來了，想 SQL Statment 真的是有活化腦細胞的作用，這次題目如下：

假設有一個 TABLE 欄位如下:   日期, 學生編號, 學生姓名, 成績，可能每隔幾天有一次考試並登入成績。
請問要如何下一個SQL SELECT 查出 每個月 前5名的學生及成績?
(用單月累積 分數最高者為當月第一名)

 

資料表如下(以#Tmp為主，#Name只是用來產生資料而已)：

CREATE TABLE #Name(ID INT,Name NVARCHAR(10))
INSERT INTO #Name VALUES(1,'索隆')
INSERT INTO #Name VALUES(2,'喬巴')
INSERT INTO #Name VALUES(3,'娜美')
INSERT INTO #Name VALUES(4,'烏索普')
INSERT INTO #Name VALUES(5,'香吉')
INSERT INTO #Name VALUES(6,'布魯克')
INSERT INTO #Name VALUES(7,'路飛')
INSERT INTO #Name VALUES(8,'羅賓')
INSERT INTO #Name VALUES(9,'佛朗基')
INSERT INTO #Name VALUES(10,'艾斯')
INSERT INTO #Name VALUES(11,'傑克')
INSERT INTO #Name VALUES(12,'雷利')


CREATE TABLE #Tmp ([Date] DATETIME, ID INT, Name NVARCHAR(10), Score INT)
DECLARE @COUNT INT 
SET @COUNT = 0

WHILE @COUNT < 1000
BEGIN
    INSERT INTO #Tmp
    SELECT TOP 1
        '2010/' + 
        CONVERT(VARCHAR(2),CONVERT(INT,RAND()*12+1)) 
        + '/' + 
        CONVERT(VARCHAR(2),CONVERT(INT,RAND()*28+1)),
        ID,
        Name,
        CONVERT(INT,RAND()*100+1)
    FROM #Name
    ORDER BY NEWID()
    
    SET @COUNT += 1
END 

DROP TABLE #Name

 

 

 

結果要長得像這樣：

 

image

 

 

 

 

 

 

 

 

 

 

 

我是這樣寫的：

 

WITH CTE AS (
    SELECT DATEPART(MONTH,A.[Date]) AS [Month],SUM(A.Score) AS TotalScore,A.ID,A.Name
    FROM #Tmp A
    GROUP BY A.ID,A.Name,DATEPART(MONTH,A.[Date])
)    
SELECT * FROM (
    SELECT *,(SELECT COUNT(*) FROM CTE A WHERE A.[Month]=CTE.[Month] AND A.TotalScore>=CTE.TotalScore) AS Grade
    FROM CTE 
) A
WHERE Grade<= 5
ORDER BY [Month],TotalScore DESC 



DROP TABLE #Tmp

AnferneeHardaway 發表在 痞客邦 留言(0) 人氣()