id number1 number2 time re
1 10-10 1 NULL 测试 1
2 10-10 1 2009-08-20 测试2
3 10-10 1 NULL 测试 3
4 10-10 1 NULL 测试4
5 10-10 1 2009-08-10 测试5
6 10-10 2 2009-08-12 测试6
7 10-10 2 2009-08-12 测试7
8 10-10 2 2009-08-12 测试8
输出结果(按number1和number2过滤,在满足time最大时取id最大的值):
id number1 number2 time re
2 10-10 1 2009-08-20 测试2
8 10-10 2 2009-08-12 测试8
1 10-10 1 NULL 测试 1
2 10-10 1 2009-08-20 测试2
3 10-10 1 NULL 测试 3
4 10-10 1 NULL 测试4
5 10-10 1 2009-08-10 测试5
6 10-10 2 2009-08-12 测试6
7 10-10 2 2009-08-12 测试7
8 10-10 2 2009-08-12 测试8
输出结果(按number1和number2过滤,在满足time最大时取id最大的值):
id number1 number2 time re
2 10-10 1 2009-08-20 测试2
8 10-10 2 2009-08-12 测试8
TIME=(SELECT MAX(TIME) FROM TB WHERE NUMBER1=T.NUMBER1 AND NUMBER2=T.NUMBER2)
select * from tb t
where not exists(select * from tb where number1=t.number1 and number2=t.number2 and
time>t.time or (time=t.time and id>t.id))--try
TIME=(SELECT top 1 TIME FROM TB WHERE NUMBER1=T.NUMBER1 AND NUMBER2=T.NUMBER2
order by TIME desc
)
*
from
tb t
where
not exists(select 1 from tb where number1=t.number1 and number2=t.number1 and time>t.time)
declare @a table(id int,number1 varchar(20),number2 int,time datetime,re varchar(20))
insert @a select
1, '10-10' , 1, NULL , '测试 1' union all select
2, '10-10' , 1 , '2009-08-20' , '测试2' union all select
3, '10-10' , 1 , NULL , '测试 3' union all select
4, '10-10' , 1 , NULL , '测试4' union all select
5, '10-10' , 1 , '2009-08-10' , '测试5' union all select
6, '10-10' , 2 , '2009-08-12' , '测试6' union all select
7, '10-10' , 2 , '2009-08-12' , '测试7' union all select
8, '10-10', 2 , '2009-08-12', '测试8'
select * from @a t
where not exists(select * from @a where number1=t.number1 and number2=t.number2 and
time>t.time or (time=t.time and id>t.id) )
and [time] is not nullid number1 number2 time re
----------- -------------------- ----------- ----------------------- --------------------
2 10-10 1 2009-08-20 00:00:00.000 测试2
8 10-10 2 2009-08-12 00:00:00.000 测试8(2 行受影响)
Drop table [tb]
Go
Create table [tb]([id] int,[number1] nvarchar(5),[number2] int,[time] datetime,[re] nvarchar(3))
Insert tb
Select 1,'10-10',1,NULL,'测试1' union all
Select 2,'10-10',1,'2009-08-20','测试2' union all
Select 3,'10-10',1,NULL,'测试3' union all
Select 4,'10-10',1,NULL,'测试4' union all
Select 5,'10-10',1,'2009-08-10','测试5' union all
Select 6,'10-10',2,'2009-08-12','测试6' union all
Select 7,'10-10',2,'2009-08-12','测试7' union all
Select 8,'10-10',2,'2009-08-12','测试8'
Go
--Select * from tb-->sql查询如下:
select * from tb t where
not exists(select 1 from tb where [number2]=t.[number2] and isnull([time],0)>isnull(t.time,0) or (isnull([time],0)=isnull(t.time,0) and id>t.id))
/*
id number1 number2 time re
----------- ------- ----------- ----------------------- ------
2 10-10 1 2009-08-20 00:00:00.000 测试2
8 10-10 2 2009-08-12 00:00:00.000 测试8(2 行受影响)
*/
借用福娃的建表。
不用not exists...为啥死活写不出来...select * from @a a where exists
(select * from @a where a.number2=number2 and a.id>id)
and time=(SELECT MAX(TIME) FROM @a where a.number2=number2 ) 多了一个ID=7
和 外面用not exists ,里面用 a.id<=id 是不一样的
select a.* from tb a,
(select number2,max(time) [time],max(id) id
from tb where time is not null group by number2) b
where a.id=b.id