27,579
社区成员
发帖
与我相关
我的任务
分享
--2005:
SELECT OI_IDF,DATADATE,DLLEFT
FROM (
SELECT RN=ROW_NUMBER()OVER(PARTITION BY OI_IDF ORDER BY DATADATE DESC),*
FROM ZPREBUY_DLLEFT
) AS T
WHERE RN<3
create table zprebuy_dlleft
( oi_idf int,
datadate datetime,
dlleft float
)
insert into zprebuy_dlleft
select 1001,'2010-03-30 14:40',101 union
select 1001,'2010-03-30 14:20',100 union
select 1001,'2010-03-30 14:30',103 union
select 1002,'2010-03-30 14:40',104 union
select 1002,'2010-03-30 14:20',105 union
select 1002,'2010-03-30 14:30',106
--2000:
SELECT *
FROM ZPREBUY_DLLEFT T
WHERE DATADATE IN(
SELECT TOP 2 DATADATE
FROM ZPREBUY_DLLEFT
WHERE OI_IDF=T.OI_IDF
ORDER BY DATADATE DESC)
--2005:
SELECT OI_IDF,DATADATE,DLLEFT
FROM (
SELECT RN=ROW_NUMBER()OVER(PARTITION BY OI_IDF ORDER BY DATADATE DESC),*
FROM ZPREBUY_DLLEFT
) AS T
WHERE RN<3
/*
OI_IDF DATADATE DLLEFT
----------- ----------------------- ----------------------
1001 2010-03-30 14:40:00.000 101
1001 2010-03-30 14:30:00.000 103
1002 2010-03-30 14:40:00.000 104
1002 2010-03-30 14:30:00.000 106
(4 行受影响)
*/
select *
from zprebuy_dlleft TB
where datadate in (select top 2 datadate
from zprebuy_dlleft where oi_idf=TB.oi_idf Order by datadate Desc)
create table zprebuy_dlleft
(
oi_idf int,
datadate datetime,
dlleft float
)
insert into zprebuy_dlleft
select 1001,'2010-03-30 14:40',101 union
select 1001,'2010-03-30 14:20',100 union
select 1001,'2010-03-30 14:30',103 union
select 1002,'2010-03-30 14:40',104 union
select 1002,'2010-03-30 14:20',105 union
select 1002,'2010-03-30 14:30',106
select oi_idf,datadate,dlleft
from
(
select *,
(select count(*)+1 from zprebuy_dlleft where oi_idf=t.oi_idf and datadate<t.datadate) row
from zprebuy_dlleft t
)
tt
where row>1
oi_idf datadate dlleft
----------- ----------------------- ----------------------
1001 2010-03-30 14:30:00.000 103
1001 2010-03-30 14:40:00.000 101
1002 2010-03-30 14:30:00.000 106
1002 2010-03-30 14:40:00.000 104
(4 行受影响)
--更正
create table zprebuy_dlleft
( oi_idf int,
datadate datetime,
dlleft float
)
insert into zprebuy_dlleft
select 1001,'2010-03-30 14:40',101 union
select 1001,'2010-03-30 14:20',100 union
select 1001,'2010-03-30 14:30',103 union
select 1002,'2010-03-30 14:40',104 union
select 1002,'2010-03-30 14:20',105 union
select 1002,'2010-03-30 14:30',106
select *
from zprebuy_dlleft t
where (select count(*)
from zprebuy_dlleft
where oi_idf=t.oi_idf and datadate>t.datadate)<2
drop table zprebuy_dlleft
/*
oi_idf datadate dlleft
----------- ----------------------- ----------------------
1001 2010-03-30 14:30:00.000 103
1001 2010-03-30 14:40:00.000 101
1002 2010-03-30 14:30:00.000 106
1002 2010-03-30 14:40:00.000 104
(4 個資料列受到影響)
*/
select *
from zprebuy_dlleft t
where datadate=(select max(datadate)
from zprebuy_dlleft where oi_idf=t.oi_idf)