34,590
社区成员
发帖
与我相关
我的任务
分享
--> --> (Roy)生成測試數據
set nocount on;
declare @T table([name] nvarchar(1),[type] int,[price] int,[company] nvarchar(1),[time] Datetime)
Insert @T
select N'A',2,2,N'A','2008-7-11 09:16:07' union all
select N'A',2,2,N'A','2008-7-11 09:16:08' union all
select N'B',2,2,N'B','2008-7-11 09:16:03' union all
select N'C',2,2,N'C','2008-7-11 09:16:04' union all
select N'A',1,1,N'A','2008-7-11 09:16:09' union all
select N'D',1,1,N'D','2008-7-11 09:16:10' union all
select N'E',1,1,N'E','2008-7-11 09:16:06'
Select
a.*
from
@T a
join
(select top 3 [company],max([time]) [time] from @T where [type]=1 and [price]=1 group by [company] order by [time] desc) b
on a.[company]=b.[company] and a.[time]=b.[time]
where
a.[type]=1 and a.[price]=1
order by a.[time] desc
select top 3 * from 表 t where not exists(
select 1 from 表 where company=t.company and time>t.time
) and type=1 and price=1
order by time desc
[code=SQL]
create table #t0711(name char,type int, price int,company char,time datetime)
insert into #t0711
select 'A',2,2,'A','2008-7-11 09:16:07' union all
select 'A',2,2,'A','2008-7-11 09:16:08' union all
select 'B',2,2,'B','2008-7-11 09:16:03' union all
select 'C',2,2,'A','2008-7-11 09:16:04' union all
select 'A',1,1,'A','2008-7-11 09:16:09' union all
select 'D',1,1,'D','2008-7-11 09:16:10' union all
select 'E',1,1,'E','2008-7-11 09:16:06'
select top 3 * from #t0711
where type=1 and price=1
order by time desc