求一条简单的sql语句

[happy_sky]

表T:

name type price company time
(产品名称) (类型) (单价) (公司名称) (加入时间)

A A1 1 A 2008-7-11 09:16:07
A A2 2 A 2008-7-11 09:16:08
B B1 3 B 2008-7-11 09:16:02
C C1 4 C 2008-7-11 09:16:03
A A3 5 A 2008-7-11 09:16:09
D D1 6 D 2008-7-11 09:16:10
E E1 7 E 2008-7-11 09:16:06

要求取前3条记录 时间是最新的 并且公司名称不能重复

结果:

D D1 6 D 2008-7-11 09:16:10
A A3 5 A 2008-7-11 09:16:09
E E1 7 E 2008-7-11 09:16:06

在线等 搞顶后立刻结贴~分不够可以再加!

[nzperfect]

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (name varchar(1),type varchar(2),price int,company varchar(1),time datetime)
insert into #T
select 'A','A1',1,'A','2008-7-11 09:16:07' union all
select 'A','A2',2,'A','2008-7-11 09:16:08' union all
select 'B','B1',3,'B','2008-7-11 09:16:02' union all
select 'C','C1',4,'C','2008-7-11 09:16:03' union all
select 'A','A3',5,'A','2008-7-11 09:16:09' union all
select 'D','D1',6,'D','2008-7-11 09:16:10' union all
select 'E','E1',7,'E','2008-7-11 09:16:06'

select top 3 a.* from #T as a
inner join 
(
select max(time) as time,name from #T group by Name
) as b on a.name=b.name and a.time=b.time order by a.time desc

/*
D	D1	6	D	2008-07-11 09:16:10.000
A	A3	5	A	2008-07-11 09:16:09.000
E	E1	7	E	2008-07-11 09:16:06.000
*/

[wzy_love_sly]

select top 3 * from (
select * from 表t t where not exists(
select 1 from 表t where 公司名称=t.公司名称 and 加入时间>t.加入时间
))t order by 加入时间

[wzy_love_sly]

if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (name varchar(1),type varchar(2),price int,company varchar(1),time datetime)
insert into #T
select 'A','A1',1,'A','2008-7-11 09:16:07' union all
select 'A','A2',2,'A','2008-7-11 09:16:08' union all
select 'B','B1',3,'B','2008-7-11 09:16:02' union all
select 'C','C1',4,'C','2008-7-11 09:16:03' union all
select 'A','A3',5,'A','2008-7-11 09:16:09' union all
select 'D','D1',6,'D','2008-7-11 09:16:10' union all
select 'E','E1',7,'E','2008-7-11 09:16:06'

select top 3 * from (
select * from #T t where not exists(
select 1 from #T where company=t.company and time>t.time
))t order by time desc

name type price company time
D D1 6 D 2008-07-11 09:16:10.000
A A3 5 A 2008-07-11 09:16:09.000
E E1 7 E 2008-07-11 09:16:06.000

借下表 :

[wzy_love_sly]

select top 3 * from #T t where not exists(
select 1 from #T where company=t.company and time>t.time
) order by time desc

name type price company time
D D1 6 D 2008-07-11 09:16:10.000
A A3 5 A 2008-07-11 09:16:09.000
E E1 7 E 2008-07-11 09:16:06.000

不用套表就行了..

[happy_sky]

大家辛苦了 测试下 先谢了.

[happy_sky]

1楼的 大哥 错误的数据 你都能该成正确的?? 佩服 BS下

[happy_sky]

OK 结贴了 经测试 1楼的答案在某些数据下 会出错.. 看来*多还是厉害啊

[nzperfect]

...

[nzperfect]

穿三角时受歧视，星少还是受歧视...