一.SQL的去重,应用场合:一个学校有10个班级,一次考试后,想要查看每个班的前3名的学生名字。下面的代码中,是只取了第一个数据,如果要前三名,把rn=1改为rn<3即可。
WITH info AS(SELECT a.[date_defaulted] as DateDefault,
a.[action_taken],
a.[action_taken_date],
a.NPHU_fruit,
a.NPHU_nonfruit,
b.[id],
b.[patient_uin],
b.[patient_name],
b.[date_registration],
b.[current_default],
ROW_NUMBER() OVER (PARTITION BY a.patient_uin ORDER BY a.[action_taken_date] DESC) AS rn
FROM [TBCU_Defaulter_log] a,
[TBCU_Defaulter] b
Where a.patient_uin = b.patient_uin and
a.date_registration=b.date_registration and
b.current_default='Y' and
(NPHU_fruit = 'Y' OR NPHU_nonfruit = 'Y') )
SELECT * From info
Where rn=1
--ORDER BY patient_uin desc
WITH info AS(SELECT a.[date_defaulted] as DateDefault,
a.