34,587
社区成员
发帖
与我相关
我的任务
分享
create table InfoData(col1 varchar(10),col2 int,fav_type int,create_time datetime,USERLEVEL int)
insert into InfoData select 'a',12,0,'2010-12-11',1
insert into InfoData select 'a',11,0,'2011-11-01',1
go
SELECT TOP 1500 * into #
FROM InfoData
WHERE ((FAV_TYPE = 0) OR
(FAV_TYPE = 9))
ORDER BY CREATE_TIME DESC
select * from # a
where not exists(select 1 from # where col1=a.col1 and col2>a.col2)
ORDER BY USERLEVEL DESC
/*
col1 col2 fav_type create_time USERLEVEL
---------- ----------- ----------- ----------------------- -----------
a 12 0 2010-12-11 00:00:00.000 1
(1 行受影响)
*/
go
drop table infodata,#
SELECT TOP 1500 * into #
FROM InfoData
WHERE ((FAV_TYPE = 0) OR
(FAV_TYPE = 9))
ORDER BY CREATE_TIME DESC
select * from # a
where not exists(select 1 from # where col1=a.col1 and col2>a.col2)
ORDER BY USERLEVEL DESC
;with cte as(
SELECT TOP 1500 *
FROM InfoData
WHERE ((FAV_TYPE = 0) OR
(FAV_TYPE = 9))
ORDER BY CREATE_TIME DESC
)select * from cte a
where not exists(select 1 from cte where col1=a.col1 and col2>a.col2)
ORDER BY USERLEVEL DESC