一张表查询,只取每个cust_id的前三条记录,求解!
Id cust_id call_date call_result
112012-03-15 09:00:00fail
212012-03-15 09:05:00number error
312012-03-15 09:10:00fail
412012-03-15 09:15:00success
522012-03-15 09:01:00fail
622012-03-15 09:06:00number error
722012-03-15 09:17:00fail
822012-03-15 09:18:00success
932012-03-15 09:04:00fail
1032012-03-15 09:09:00number error
1132012-03-15 09:19:00fail
1232012-03-15 09:22:22success
1342012-03-15 09:07:00fail
1442012-03-15 09:21:40success
[解决办法]
--> 测试数据:[tbl]if object_id('[tbl]') is not null drop table [tbl]create table [tbl]([Id] int,[cust_id] int,[call_date] datetime,[call_result] varchar(7),[C5] varchar(5))insert [tbl]select 1,1,'2012-03-15 09:00:00','fail',null union allselect 2,1,'2012-03-15 09:05:00','number','error' union allselect 3,1,'2012-03-15 09:10:00','fail',null union allselect 4,1,'2012-03-15 09:15:00','success',null union allselect 5,2,'2012-03-15 09:01:00','fail',null union allselect 6,2,'2012-03-15 09:06:00','number','error' union allselect 7,2,'2012-03-15 09:17:00','fail',null union allselect 8,2,'2012-03-15 09:18:00','success',null union allselect 9,3,'2012-03-15 09:04:00','fail',null union allselect 10,3,'2012-03-15 09:09:00','number','error' union allselect 11,3,'2012-03-15 09:19:00','fail',null union allselect 12,3,'2012-03-15 09:22:22','success',null union allselect 13,4,'2012-03-15 09:07:00','fail',null union allselect 14,4,'2012-03-15 09:21:40','success',nullselect [Id] ,[cust_id],[call_date] ,[call_result] from(select ROW_NUMBER()OVER(partition by [cust_id] order by [call_date] desc) as num,* from tbl)a where num<=3/*Id cust_id call_date call_result4 1 2012-03-15 09:15:00.000 success3 1 2012-03-15 09:10:00.000 fail2 1 2012-03-15 09:05:00.000 number8 2 2012-03-15 09:18:00.000 success7 2 2012-03-15 09:17:00.000 fail6 2 2012-03-15 09:06:00.000 number12 3 2012-03-15 09:22:22.000 success11 3 2012-03-15 09:19:00.000 fail10 3 2012-03-15 09:09:00.000 number14 4 2012-03-15 09:21:40.000 success13 4 2012-03-15 09:07:00.000 fail*/按时间取每个cus_id的前三