VB6总结III & SQL

最新推荐文章于 2025-05-29 09:11:38 发布

原创最新推荐文章于 2025-05-29 09:11:38 发布 · 369 阅读

0 ·

CC 4.0 BY-SA版权

SQL 同时被 2 个专栏收录

17 篇文章

订阅专栏

16 篇文章

订阅专栏

这篇博客探讨了如何使用SQL进行数据去重，以满足特定场景如获取每个班级的前三名学生。同时，文章还介绍了如何在VB6中查询一个patient对应多个log记录时，获取每个patient的最新log信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一.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.[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] asc) 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.[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] asc) 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 
						  (action_taken = 'NPHU') )
						  SELECT * From info 
						  Where rn=1

二.一个patient表，一个log表，一个patient对应多个log。grid要显示符合某一条件的每一个patient的最新log的信息，SQL语句如下

    strSQL = "WITH info AS"
    strSQL = strSQL & "(SELECT a.[date_defaulted] as DateDefault, a.[date_resumed_attended] as DateResume, a.[action_taken],a.[NPHU_effe],a.[NPHU_noneffe],a.[action_taken_date],a.[refer_NPHU_date],"
    strSQL = strSQL & "b.[id],b.[patient_uin],b.[patient_name],b.[date_registration],b.[current_default],"
    strSQL = strSQL & "ROW_NUMBER() OVER (PARTITION BY a.patient_uin ORDER BY a.[action_taken_date] DESC) AS rn "
    strSQL = strSQL & "FROM [TBCU_Defaulter_log] a,[TBCU_Defaulter] b "
    strSQL = strSQL & "Where a.patient_uin = b.patient_uin and a.date_registration=b.date_registration and b.current_default='Y')"
    strSQL = strSQL & "SELECT * From info Where rn=1"