请教一SQL

显示全部楼层 · 2012-1-4 11:53:54

表名：YKT_VISIT
ID
序号
GUEST_ID
访客ID
ARRIVAL_DATE
到达时间
EMPLOYEE_ID
员工ID
CONFIRM_STATUS
确认状态
VISIT_FLOOR
访问楼层
VISIT_NUMBER
访客人数
RESERVATION_ID
CHAR(32)
预约ID
我要把每一个
相同的“访客ID”最后“到达时间”的那一条记录取出来怎么做啊？

千问 · 2012-1-4 11:53:54

用ROW_NUMBER()

千问 · 2012-1-4 11:53:54

具体怎么写啊？我不会写，谢谢了

千问 · 2012-1-4 11:53:54

with t as (select t.*,row_number() over(partition by GUEST_ID order by ARRIVAL_DATE desc) rn from YKT_VISIT )
select * from t where rn=1

千问 · 2012-1-4 11:53:54

还是楼上的牛，比我的办法好多鸟！
select yv.* from ykt_visit yv,
(select guest_id,max(ARRIVAL_DATE) ARRIVAL_DATE fromykt_visit
group by guest_id)a
where a.guest_id=yv.guest_id
and a.ARRIVAL_DATE=yv.ARRIVAL_DATE

没有数据所以没有测试过，大概应该是这个样子的。
[ 本帖最后由顾而不问于 2008-6-11 15:47 编辑 ]

千问 · 2012-1-4 11:53:54

These questions have been asked many times, there are many ways to get the same result!

千问 · 2012-1-4 11:53:54

哈哈 These questions have been asked many times,

千问 · 2012-1-4 11:53:54

this question has been asked mang times;

千问 · 2012-1-4 11:53:54

http://www.itpub.net/690125,1.html

千问 · 2012-1-4 11:53:54

yes
many times