表T: name type price company time
(产品名称) (类型) (单价) (公司名称) (加入时间) A 2 2 A 2008-7-11 09:16:07
A 2 2 A 2008-7-11 09:16:08
B 2 2 B 2008-7-11 09:16:03
C 2 2 C 2008-7-11 09:16:04
A 1 1 A 2008-7-11 09:16:09
D 1 1 D 2008-7-11 09:16:10
E 1 1 E 2008-7-11 09:16:06 要求取前3条记录 时间是最新的 单价等于1 类型等1 并且公司名称不能重复 结果: D D1 1 D 2008-7-11 09:16:10
A A3 1 A 2008-7-11 09:16:09
E E1 1 E 2008-7-11 09:16:06 在线等,分不够再加~~
(产品名称) (类型) (单价) (公司名称) (加入时间) A 2 2 A 2008-7-11 09:16:07
A 2 2 A 2008-7-11 09:16:08
B 2 2 B 2008-7-11 09:16:03
C 2 2 C 2008-7-11 09:16:04
A 1 1 A 2008-7-11 09:16:09
D 1 1 D 2008-7-11 09:16:10
E 1 1 E 2008-7-11 09:16:06 要求取前3条记录 时间是最新的 单价等于1 类型等1 并且公司名称不能重复 结果: D D1 1 D 2008-7-11 09:16:10
A A3 1 A 2008-7-11 09:16:09
E E1 1 E 2008-7-11 09:16:06 在线等,分不够再加~~
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (name varchar(1),type int,price int,company varchar(1),time datetime)
insert into #T
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,'C','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 #T as t
where time=(select max(time) from #T where name=t.name)
order by time desc/*
name type price company time
---- ----------- ----------- ------- -----------------------
D 1 1 D 2008-07-11 09:16:10.000
A 1 1 A 2008-07-11 09:16:09.000
E 1 1 E 2008-07-11 09:16:06.000(3 行受影响)
*/
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 *
from @T a
where [type]=1 and [time] in(select top 3 [time] from @T where [type]=a.[type] order by [time] desc) order by [time] desc
name type price company time
---- ----------- ----------- ------- -----------------------
D 1 1 D 2008-07-11 09:16:10.000
A 1 1 A 2008-07-11 09:16:09.000
E 1 1 E 2008-07-11 09:16:06.000
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
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 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
name type price company time
---- ----------- ----------- ------- -----------------------
D 1 1 D 2008-07-11 09:16:10.000
A 1 1 A 2008-07-11 09:16:09.000
E 1 1 E 2008-07-11 09:16:06.000
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
insert into tb
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,'C','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 * from tbselect top 3 * from tb tt where exists(select * from tb where [type]=tt.[type] and [type]=1 and
price=tt.price and price=1 and company <>tt.company) order by [time] desc
Set Nocount On
declare @1 table([name] nvarchar(1),[type] int,[price] int,[company] nvarchar(1),[time] Datetime)
Insert @1
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'
--考虑条件的先后顺序不一样,写法就不一样
--以下是先考虑单价等于1 类型等1,再考虑公司名称不能重复,然后是考虑时间排序,最后考虑取前3条Select Top 3 * from @1 a Where [price]=1 And [type]=1
And [time] In(Select top(3) [time] From @1 x Where [company]=a.[company] And [price]=1 And [type]=1 And [time]=(Select Max([time]) From @1 Where [company]=x.[company] And [price]=1 And [type]=1) Order By [time] Desc)
Order By [time] desc
/*
name type price company time
---- ----------- ----------- ------- -----------------------
D 1 1 D 2008-07-11 09:16:10.000
A 1 1 A 2008-07-11 09:16:09.000
E 1 1 E 2008-07-11 09:16:06.000
*/
create table T (name varchar(1),type int,price int,company varchar(1),time datetime)
insert into T
select 'A',1,1,'E','2008-7-11 09:16:06' union all
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,'C','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 t as t1
where time=(select max(time) from t where name=t1.name) and type=1 and price=1
order by name