找出重覆資料中某欄位最大值的資料

2010-04-22

20454
0
SQL練習

找出重覆資料中某欄位最大值的資料

問題

假如有個學員資料，其中只有登記日期不同，其他欄位都相同，我們要如何找出每個學員資料登記日期最大的那一筆出來呢?

資料準備

--找出重覆資料中某欄位最大值的資料
IF  EXISTS (SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[STUDENT1]') AND type in (N'U'))
DROP TABLE [dbo].[STUDENT1]
GO


CREATE TABLE [dbo].[STUDENT1](
    [S_ID] [varchar](50) NOT NULL, -- 學員代號
    [S_NAME] [nvarchar](50) NOT NULL, --學員姓名
    [REG_DATE] [datetime] NOT NULL, --登記日期
 CONSTRAINT [PK_STUDENT1] PRIMARY KEY CLUSTERED 
 (
    [S_ID] ASC,
    [REG_DATE] ASC
 ) ON [PRIMARY]
)  
GO

--資料準備
INSERT INTO [STUDENT1]([S_ID] ,[S_NAME] ,[REG_DATE])
VALUES('S0001', N'亂馬客', CAST('2010/01/01' AS DATETIME)) 

INSERT INTO [STUDENT1]([S_ID] ,[S_NAME] ,[REG_DATE])
VALUES('S0001', N'亂馬客', CAST('2010/02/01' AS DATETIME))

INSERT INTO [STUDENT1]([S_ID] ,[S_NAME] ,[REG_DATE])
VALUES('S0001', N'亂馬客', CAST('2010/03/01' AS DATETIME)) 

INSERT INTO [STUDENT1]([S_ID] ,[S_NAME] ,[REG_DATE])
VALUES('S0001', N'亂馬客', CAST('2010/04/01' AS DATETIME))

INSERT INTO [STUDENT1]([S_ID] ,[S_NAME] ,[REG_DATE])
VALUES('S0002', N'造雨人', CAST('2010/01/01' AS DATETIME))

INSERT INTO [STUDENT1]([S_ID] ,[S_NAME] ,[REG_DATE])
VALUES('S0002', N'造雨人', CAST('2010/03/01' AS DATETIME))

INSERT INTO [STUDENT1]([S_ID] ,[S_NAME] ,[REG_DATE])
VALUES('S0003', N'阿光', CAST('2010/04/01' AS DATETIME))

INSERT INTO [STUDENT1]([S_ID] ,[S_NAME] ,[REG_DATE])
VALUES('S0003', N'阿光', CAST('2010/05/01' AS DATETIME))

實作

如上述資料，我們分別要找出S001,亂馬客,2010/04/01、S002,造雨人,2010/03/01及S003,阿光,2010/05/01的資料! 因為要最大值，所以就想到了用GROUP+MAX函式來處理啦!
SELECT A.S_ID, A.S_NAME, MAX(A.REG_DATE) AS MAX_REG_DATE FROM [STUDENT1] A (NOLOCK)
GROUP BY A.S_ID, A.S_NAME