表内容如下
code data num
101 2009-06-1 1
101 2009-06-2 2
101 2009-06-3 3
102 2009-06-3 4
102 2009-06-5 9
102 2009-06-2 7
103 2009-06-8 8
103 2009-07-1 6
要求得到每个编码最大日期的数量 如下:code data num
101 2009-06-3 3
102 2009-06-5 9
103 2009-07-1 6
code data num
101 2009-06-1 1
101 2009-06-2 2
101 2009-06-3 3
102 2009-06-3 4
102 2009-06-5 9
102 2009-06-2 7
103 2009-06-8 8
103 2009-07-1 6
要求得到每个编码最大日期的数量 如下:code data num
101 2009-06-3 3
102 2009-06-5 9
103 2009-07-1 6
*
from
tb t
where
not exists(select 1 from tb where code=t.code and data>t.data)
*
from
tb t
where
data=(select max(data) from tb where code=t.code)
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([code] int,[data] datetime,[num] int)
insert [tb]
select 101,'2009-06-1',1 union all
select 101,'2009-06-2',2 union all
select 101,'2009-06-3',3 union all
select 102,'2009-06-3',4 union all
select 102,'2009-06-5',9 union all
select 102,'2009-06-2',7 union all
select 103,'2009-06-8',8 union all
select 103,'2009-07-1',6
---查询---
select
code,
convert(varchar(10),data,120) as data,
num
from
tb t
where
not exists(select 1 from tb where code=t.code and data>t.data)---结果---
code data num
----------- ---------- -----------
101 2009-06-03 3
102 2009-06-05 9
103 2009-07-01 6(所影响的行数为 3 行)
from table
group by code
where not exists(select 1 from tb where code=a.code and data>a.data)