查询最近2条记录

apple1003 2010-03-30 03:41:27
表:
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

结果:
我想查询同一oi_df的datadate最近2条记录。
如:1001,'2010-03-30 14:30',103
1001,'2010-03-30 14:40',101
1002,'2010-03-30 14:30',106
1002,'2010-03-30 14:40',104
请高手指点,谢谢~
...全文
134 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
永生天地 2010-03-30
  • 打赏
  • 举报
回复
[Quote=引用 5 楼 zhousq00 的回复:]
SQL code

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)
[/Quote]
为什么不用这个 top 2
feixianxxx 2010-03-30
  • 打赏
  • 举报
回复
--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

2005版本 支持这个。。。
mylover002 2010-03-30
  • 打赏
  • 举报
回复
;with temptb as
(
select dense_rank() over (partition by oi_idf order by datadate desc) as xuhao, *
from zprebuy_dlleft
)

select oi_idf,datadate,dlleft from temptb where xuhao<=2

用上面的sql语句即可以了,不过要是sql 2000以上版本
htl258_Tony 2010-03-30
  • 打赏
  • 举报
回复
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 行受影响)
*/
zhousq00 2010-03-30
  • 打赏
  • 举报
回复

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)

这样应该是没问题的!
水族杰纶 2010-03-30
  • 打赏
  • 举报
回复
[Quote=引用 3 楼 ws_hgo 的回复:]
SQL code
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',10……
[/Quote]
如果同類記錄多於3條
或者同類只有1條
就滿足不了
ws_hgo 2010-03-30
  • 打赏
  • 举报
回复
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 行受影响)
水族杰纶 2010-03-30
  • 打赏
  • 举报
回复
--更正
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 個資料列受到影響)

*/
水族杰纶 2010-03-30
  • 打赏
  • 举报
回复
select * 
from zprebuy_dlleft t
where datadate=(select max(datadate)
from zprebuy_dlleft where oi_idf=t.oi_idf)

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧