表A如下
ID 日期 产品 数量
3 2008-06-01 A 10
5 2008-06-01 B 20
8 2008-06-01 C 5
7 2008-06-05 D 10
10 2008-06-05 A 20
4 2008-06-21 C 20
6 2008-06-21 D 12
1 2008-06-25 E 8
9 2008-06-25 C 25
2 2008-06-27 A 50要求查询结果如下(取得每个产品的最后一条记录)
ID 日期 产品 数量
5 2008-06-01 B 20
6 2008-06-21 D 12
1 2008-06-25 E 8
9 2008-06-25 C 25
2 2008-06-27 A 50
ID 日期 产品 数量
3 2008-06-01 A 10
5 2008-06-01 B 20
8 2008-06-01 C 5
7 2008-06-05 D 10
10 2008-06-05 A 20
4 2008-06-21 C 20
6 2008-06-21 D 12
1 2008-06-25 E 8
9 2008-06-25 C 25
2 2008-06-27 A 50要求查询结果如下(取得每个产品的最后一条记录)
ID 日期 产品 数量
5 2008-06-01 B 20
6 2008-06-21 D 12
1 2008-06-25 E 8
9 2008-06-25 C 25
2 2008-06-27 A 50
http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html
select 1 from 表a where 产品=t.产品 and 日期>t.日期
)
--> 测试数据: #T1
if object_id('tempdb.dbo.#T1') is not null drop table #T1
create table #T1 (ID int,日期 datetime,产品 varchar(1),数量 int)
insert into #T1
select 3,'2008-06-01','A',10 union all
select 5,'2008-06-01','B',20 union all
select 8,'2008-06-01','C',5 union all
select 7,'2008-06-05','D',10 union all
select 10,'2008-06-05','A',20 union all
select 4,'2008-06-21','C',20 union all
select 6,'2008-06-21','D',12 union all
select 1,'2008-06-25','E',8 union all
select 9,'2008-06-25','C',25 union all
select 2,'2008-06-27','A',50select * from #T1 as a where id in (select top 1 (id) from #T1 where 产品=a.产品 order by 日期 desc)
/*
-----------
5 2008-06-01 00:00:00.000 B 20
6 2008-06-21 00:00:00.000 D 12
1 2008-06-25 00:00:00.000 E 8
9 2008-06-25 00:00:00.000 C 25
2 2008-06-27 00:00:00.000 A 50
*/
Insert @A
select 3,'2008-06-01',N'A',10 union all
select 5,'2008-06-01',N'B',20 union all
select 8,'2008-06-01',N'C',5 union all
select 7,'2008-06-05',N'D',10 union all
select 10,'2008-06-05',N'A',20 union all
select 4,'2008-06-21',N'C',20 union all
select 6,'2008-06-21',N'D',12 union all
select 1,'2008-06-25',N'E',8 union all
select 9,'2008-06-25',N'C',25 union all
select 2,'2008-06-27',N'A',50
Select
*
from
@A a
where [ID]=(select top 1 ID from @A where [产品]=a.[产品] order by [日期] desc)
(10 個資料列受到影響)
ID 日期 产品 数量
----------- ----------------------- ---- -----------
5 2008-06-01 00:00:00.000 B 20
6 2008-06-21 00:00:00.000 D 12
1 2008-06-25 00:00:00.000 E 8
9 2008-06-25 00:00:00.000 C 25
2 2008-06-27 00:00:00.000 A 50(5 個資料列受到影響)
where 日期=(select max(日期) from 表a where 产品=t.产品)
insert into @tb select 3,'2008-06-01','A','10'
insert into @tb select 5,'2008-06-01','B','20'
insert into @tb select 8,'2008-06-01','C','5'
insert into @tb select 7,'2008-06-05','D','10'
insert into @tb select 10,'2008-06-05','A','20'
insert into @tb select 4,'2008-06-21','C','20'
insert into @tb select 6,'2008-06-21','D','12'
insert into @tb select 1,'2008-06-25','E','8'
insert into @tb select 9,'2008-06-25','C','25'
insert into @tb select 2,'2008-06-27','A','50'select * from @tb t where not exists(
select 1 from @tb where 产品=t.产品 and 日期>t.日期
)
可能我没表述清楚,因为同一产品在同一天可能有好几条记录
所以只有roy_88和perfectaction 满足我的要求