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

2010-04-22

11512
0
SQL練習

問題

之前「找出重覆資料中某欄位最大值的資料」是欄位只有登記日期不同，如果說有多個欄位不同，要找出學員登記日期最大的那筆資料要如何做呢?

資料準備

    WHERE object_id = OBJECT_ID(N'[dbo].[STUDENT2]') AND type in (N'U'))
DROP TABLE [dbo].[STUDENT2]
GO

CREATE TABLE [dbo].[STUDENT2](
    [S_ID] [varchar](50) NOT NULL, -- 學員代號
    [S_NAME] [nvarchar](50) NOT NULL, --學員姓名
    [REG_DATE] [datetime] , --登記日期
    [S_ADDRESS] NVARCHAR(100), --學員住址
    [ID_SEQ] [INT] IDENTITY(1,1)--流水號
 CONSTRAINT [PK_STUDENT2] PRIMARY KEY CLUSTERED 
 (
    [ID_SEQ] ASC
 ) ON [PRIMARY]
)  
GO
--資料準備
INSERT INTO [STUDENT2]([S_ID] ,[S_NAME] ,[REG_DATE],[S_ADDRESS])
VALUES('S0001', N'亂馬客', CAST('2010/01/01' AS DATETIME), N'台北市中山北路100號')

INSERT INTO [STUDENT2]([S_ID] ,[S_NAME] ,[REG_DATE],[S_ADDRESS])
VALUES('S0001', N'亂馬客', CAST('2010/02/01' AS DATETIME), N'台北市中山北路101號')

INSERT INTO [STUDENT2]([S_ID] ,[S_NAME] ,[REG_DATE],[S_ADDRESS])
VALUES('S0001', N'亂馬客', CAST('2010/03/01' AS DATETIME), N'台北市中山北路102號')

INSERT INTO [STUDENT2]([S_ID] ,[S_NAME] ,[REG_DATE],[S_ADDRESS])
VALUES('S0001', N'亂馬客', CAST('2010/04/01' AS DATETIME), N'台北市中山北路103號')

INSERT INTO [STUDENT2]([S_ID] ,[S_NAME] ,[REG_DATE],[S_ADDRESS])
VALUES('S0001', N'亂馬客', NULL , N'台北市中山北路104號')

INSERT INTO [STUDENT2]([S_ID] ,[S_NAME] ,[REG_DATE],[S_ADDRESS])
VALUES('S0002', N'造雨人', CAST('2010/01/01' AS DATETIME), N'台北市忠孝東路101號') 

INSERT INTO [STUDENT2]([S_ID] ,[S_NAME] ,[REG_DATE],[S_ADDRESS])
VALUES('S0002', N'造雨人', CAST('2010/03/01' AS DATETIME), N'台北市忠孝東路102號') 

INSERT INTO [STUDENT2]([S_ID] ,[S_NAME] ,[REG_DATE],[S_ADDRESS])
VALUES('S0002', N'造雨人',  NULL, N'台北市忠孝東路103號') 

INSERT INTO [STUDENT2]([S_ID] ,[S_NAME] ,[REG_DATE],[S_ADDRESS])
VALUES('S0003', N'阿光', CAST('2010/04/01' AS DATETIME), N'台北市敦化南路101號') 

INSERT INTO [STUDENT2]([S_ID] ,[S_NAME] ,[REG_DATE],[S_ADDRESS])
VALUES('S0003', N'阿光', CAST('2010/05/01' AS DATETIME), N'台北市敦化南路102號') 

INSERT INTO [STUDENT2]([S_ID] ,[S_NAME] ,[REG_DATE],[S_ADDRESS])
VALUES('S0003', N'阿光', NULL , N'台北市敦化南路103號') 

實作

因為這次多了地址欄位，所以就先把找出學員及最大登記日期找出來後，再跟原資料表JOIN即可找出所需的資料。
SELECT A.*
FROM [STUDENT2] A (NOLOCK)
INNER JOIN (
SELECT B.S_ID, MAX(B.REG_DATE) AS MAX_REG_DATE 
FROM [STUDENT2] B (NOLOCK)
GROUP BY B.S_ID) C
ON A.S_ID = C.S_ID AND A.REG_DATE = C.MAX_REG_DATE
ORDER BY A.S_ID