现通过查询得出这样的数据
如:
用户(user) 年龄(age) 城市(city) 彩信收取时间(mmsTime) 状态(state)
小王 29 上海 20080620 01
小王 29 上海 20080621 03
小王 29 上海 20080622 01
小王 29 上海 20080623 04
李某 30 扬州 20080619 03
李某 30 扬州 20080620 01
比尔 50 广州 20080622 02
..........我想得到如下数据:
用户(user) 年龄(age) 城市(city) 彩信收取时间(mmsTime) 状态(state)
小王 29 上海 20080623 04
李某 30 扬州 20080620 01
比尔 50 广州 20080622 02
..........
就是说按时间来筛选最后一条记录,该如何做?谢谢!
如:
用户(user) 年龄(age) 城市(city) 彩信收取时间(mmsTime) 状态(state)
小王 29 上海 20080620 01
小王 29 上海 20080621 03
小王 29 上海 20080622 01
小王 29 上海 20080623 04
李某 30 扬州 20080619 03
李某 30 扬州 20080620 01
比尔 50 广州 20080622 02
..........我想得到如下数据:
用户(user) 年龄(age) 城市(city) 彩信收取时间(mmsTime) 状态(state)
小王 29 上海 20080623 04
李某 30 扬州 20080620 01
比尔 50 广州 20080622 02
..........
就是说按时间来筛选最后一条记录,该如何做?谢谢!
select
gsm_user_yz.msisdn,
gsm_user_yz.state,
gsm_user_yz.suspend_date,
p_customer_yz.customer_name,
p_customer_yz.sex,
user_smscall_yz.deal_code,
user_smscall_yz.end_date
from
yzzw.gsm_user_yz,
yzzw.p_customer_yz,
yzzw.user_smscall_yz
where
gsm_user_yz.customer_id = p_customer_yz.customer_id
and gsm_user_yz.gsm_user_id = user_smscall_yz.gsm_user_id
and gsm_user_yz.msisdn in(...,...,...)最好在这个基础上改写一下,谢谢了!
select c.* from (select a.*,row_number() over(partition by user,age,city order by mmsTime desc) rn) c where rn=1
select c.* from (select a.*,row_number() over(partition by user,age,city order by mmsTime desc) rn from a) c where c.rn=1
select c.user,c.age,c.city,c.mmsTime,c.state from (select a.*,row_number() over(partition by user,age,city order by mmsTime desc) rn from a) c where c.rn=1
from (select a.*,row_number() over(partition by users,age,city order by mmsTime desc) rn from
(
select
gsm_user_yz.msisdn,
gsm_user_yz.state,
gsm_user_yz.suspend_date,
p_customer_yz.customer_name,
p_customer_yz.sex,
user_smscall_yz.deal_code,
user_smscall_yz.end_date
from
yzzw.gsm_user_yz,
yzzw.p_customer_yz,
yzzw.user_smscall_yz
where
gsm_user_yz.customer_id = p_customer_yz.customer_id
and gsm_user_yz.gsm_user_id = user_smscall_yz.gsm_user_id
and gsm_user_yz.msisdn in(...,...,...)
)as a) as c
where c.rn=1数据库菜鸟,不要见笑!
where mmstime in (select max(mmstime) from tb b
where a.user=b.user)
select n,max(p) from test GROUP BY n
这样就可以了!唉,笨啊!