数据表 Aid fwid khid dtime
20091218203348908 20091129230406156 20091109150709984 2010-01-23 00:00:00
20091218203419049 20091129230406156 20091109150709984 2010-02-05 00:00:00
20091231221734046 20091129230411546 20091110165311515 2009-12-14 00:00:00
20100101141948187 20091129230411546 20091110165311515 2010-01-30 00:00:00想查询返回出这样的结果
20091218203419049 20091129230406156 20091109150709984 2010-02-05 00:00:00
20100101141948187 20091129230411546 20091110165311515 2010-01-30 00:00:00就是每个fwid中日期最近的那一条数据。
麻烦各位!谢谢了!!
20091218203348908 20091129230406156 20091109150709984 2010-01-23 00:00:00
20091218203419049 20091129230406156 20091109150709984 2010-02-05 00:00:00
20091231221734046 20091129230411546 20091110165311515 2009-12-14 00:00:00
20100101141948187 20091129230411546 20091110165311515 2010-01-30 00:00:00想查询返回出这样的结果
20091218203419049 20091129230406156 20091109150709984 2010-02-05 00:00:00
20100101141948187 20091129230411546 20091110165311515 2010-01-30 00:00:00就是每个fwid中日期最近的那一条数据。
麻烦各位!谢谢了!!
from a t
where not exists(select 1 from a where fwid=t.fwid and dtime>t.dtime)
if object_id('[a]') is not null drop table [a]
go
create table [a]([id] bigint,[fwid] bigint,[khid] bigint,[dtime] datetime)
insert [a]
select 20091218203348908,20091129230406156,20091109150709984,'2010-01-23 00:00:00' union all
select 20091218203419049,20091129230406156,20091109150709984,'2010-02-05 00:00:00' union all
select 20091231221734046,20091129230411546,20091110165311515,'2009-12-14 00:00:00' union all
select 20100101141948187,20091129230411546,20091110165311515,'2010-01-30 00:00:00'
---查询---
select *
from a t
where not exists(select 1 from a where fwid=t.fwid and dtime>t.dtime)---结果---
id fwid khid dtime
-------------------- -------------------- -------------------- ------------------------------------------------------
20091218203419049 20091129230406156 20091109150709984 2010-02-05 00:00:00.000
20100101141948187 20091129230411546 20091110165311515 2010-01-30 00:00:00.000(所影响的行数为 2 行)
from ta a
where not exists(select 1 from ta where a.fwid = fwid and id >a.id)