一個SQL查詢問題，立即解決，立即給分
表A:
ID ITEMID TRANSDATE QTY
1 A 2012-02-17 0.3
2 A 2012-02-17 0.4
3 A 2012-02-11 0.8
4 A 2012-02-16 0.5
5 B 2012-02-12 0.2
6 B 2012-01-15 0.45
7 B 2012-02-11 0.82
8 B 2012-01-12 0.73
... ... ... ...
根据ITEMID取日期最新的前三条：效果如下
ID ITEMID TRANSDATE QTY
1 A 2012-02-17 0.3
2 A 2012-02-17 0.4
4 A 2012-02-16 0.5
5 B 2012-02-12 0.2
6 B 2012-01-15 0.45
7 B 2012-02-11 0.82
... ... ... ...
[解决办法]
select * from(select row_number() over(partition by ITEMID order by TRANSDATE desc) as pg,ID,ITEMID,TRANSDATE,QTYfrom 表A) t where pg<=3
[解决办法]
select tI.D, t.ITEMID,t.TRANSDATE,t.QTY from
(select row_number() over(partition by ITEMID order by TRANSDATE desc) as num,ID,ITEMID,TRANSDATE,QTY
from 表A
) t where num<=3
[解决办法]
/*
表A:
ID ITEMID TRANSDATE QTY
1 A 2012-02-17 0.3
2 A 2012-02-17 0.4
3 A 2012-02-11 0.8
4 A 2012-02-16 0.5
5 B 2012-02-12 0.2
6 B 2012-01-15 0.45
7 B 2012-02-11 0.82
8 B 2012-01-12 0.73
*/
go
if OBJECT_ID('tbl')is not null
drop table tbl
go
create table tbl(
ID int,
ITEMID varchar(2),
TRANSDATE datetime,
QTY float
)
go
insert tbl
select 1,'A','2012-02-17',0.3 union all
select 2,'A','2012-02-17',0.4 union all
select 3,'A','2012-02-11',0.8 union all
select 4,'A','2012-02-16',0.5 union all
select 5,'B','2012-02-12',0.2 union all
select 6,'B','2012-01-15',0.45 union all
select 7,'B','2012-02-11',0.82 union all
select 8,'B','2012-01-12',0.73
select ID,ITEMID,TRANSDATE,QTY from
(select ROW_NUMBER()OVER(partition by ITEMID order by TRANSDATE desc) as num,
* from tbl)a where num<=3
/*
IDITEMIDTRANSDATEQTY
1A2012-02-17 00:00:00.0000.3
2A2012-02-17 00:00:00.0000.4
4A2012-02-16 00:00:00.0000.5
5B2012-02-12 00:00:00.0000.2
7B2012-02-11 00:00:00.0000.82
6B2012-01-15 00:00:00.0000.45
*/
