有这个一个表:
会员卡号 入会时间 会员手机
1 2011-08-02 13788888888
2 2011-09-03 13511111111
3 2011-10-11 13777777777
4 2011-10-22 13788888888
5 2011-11-02 13511111111
6 2011-12-12 13511111111
7 2012-01-02 13788888888
8 2012-01-07 13777777777
9 2012-02-09 13788888888同一个手机号可以办理多个会员,现在我想按照手机号码唯一进行查询,查询一共有多少个会员入会,并且显示出最后入会时间的卡号 如下
会员卡号 入会时间 会员手机
6 2011-12-12 13511111111
8 2012-01-07 13777777777
9 2012-02-09 13788888888
请问怎样用SQL语句实现?
会员卡号 入会时间 会员手机
1 2011-08-02 13788888888
2 2011-09-03 13511111111
3 2011-10-11 13777777777
4 2011-10-22 13788888888
5 2011-11-02 13511111111
6 2011-12-12 13511111111
7 2012-01-02 13788888888
8 2012-01-07 13777777777
9 2012-02-09 13788888888同一个手机号可以办理多个会员,现在我想按照手机号码唯一进行查询,查询一共有多少个会员入会,并且显示出最后入会时间的卡号 如下
会员卡号 入会时间 会员手机
6 2011-12-12 13511111111
8 2012-01-07 13777777777
9 2012-02-09 13788888888
请问怎样用SQL语句实现?
select t.* from tb t where 入会时间 = (select max(入会时间) from tb where 会员手机 = t.会员手机)select t.* from tb t where not exists (select 1 from tb where 会员手机 = t.会员手机 and 入会时间 > t.入会时间)
insert into tb values(1 ,'2011-08-02', '13788888888')
insert into tb values(2 ,'2011-09-03', '13511111111')
insert into tb values(3 ,'2011-10-11', '13777777777')
insert into tb values(4 ,'2011-10-22', '13788888888')
insert into tb values(5 ,'2011-11-02', '13511111111')
insert into tb values(6 ,'2011-12-12', '13511111111')
insert into tb values(7 ,'2012-01-02', '13788888888')
insert into tb values(8 ,'2012-01-07', '13777777777')
insert into tb values(9 ,'2012-02-09', '13788888888')
goselect t.* from tb t where 入会时间 = (select max(入会时间) from tb where 会员手机 = t.会员手机) order by t.会员手机select t.* from tb t where not exists (select 1 from tb where 会员手机 = t.会员手机 and 入会时间 > t.入会时间) order by t.会员手机drop table tb/*
会员卡号 入会时间 会员手机
----------- ------------------------------------------------------ --------------------
6 2011-12-12 00:00:00.000 13511111111
8 2012-01-07 00:00:00.000 13777777777
9 2012-02-09 00:00:00.000 13788888888(所影响的行数为 3 行)会员卡号 入会时间 会员手机
----------- ------------------------------------------------------ --------------------
6 2011-12-12 00:00:00.000 13511111111
8 2012-01-07 00:00:00.000 13777777777
9 2012-02-09 00:00:00.000 13788888888(所影响的行数为 3 行)
*/
select row_number()over(partition by ... order by ... desc) as num,*
from tbl
)a where num=1