sql查询2个表中的重复记录，重复记录里时间最大的那个。。。在线等，谢谢
现在有test1,test2 2个表，表的结构分别是
Id,memberId,CreateTime 3个字段
比如test1里有数据
1 22 2013-4-9
2 23 2013-4-6
3 24 2013-4-2
4 22 2013-4-11
5 6 2013-4-9
比如test2里有数据
1 22 2013-4-12
2 5 2013-4-8
3 33 2013-4-12
4 24 2013-4-22
5 6 2013-4-18
我现在想取的数据是：test1跟test2表里不重复memberId的数据，并且取CreateTime最大的，按照上面的案例列表，我想要的数据应该是
22 2013-4-12
23 2013-4-6
24 2013-4-22
6 2013-4-18
5 2013-4-8
33 2013-4-12
然后我再详细分析下：
1 22 2013-4-9 重复22，但是CreateTime不是最大的
2 23 2013-4-6
3 24 2013-4-2 重复24，但是CreateTime不是最大的
4 22 2013-4-11 重复22，但是CreateTime不是最大的
5 6 2013-4-18
比如test2里有数据
1 22 2013-4-12
2 5 2013-4-8
3 33 2013-4-12
4 24 2013-4-22
5 6 2013-4-9 重复6，但是CreateTime不是最大的
我写的应该很明白了，希望懂的给解答下，谢谢
sql mssql
[解决办法]
create table test1(id int,memberId int,CreateTime date)
create table test2(id int,memberId int,CreateTime date)
insert into test1
select 1,22,'2013-4-9'
union all select 2,23,'2013-4-6'
union all select 3,24,'2013-4-2'
union all select 4,22,'2013-4-11'
union all select 5,6,'2013-4-9'
insert into test2
select 1,22,'2013-4-12'
union all select 2,5,'2013-4-8'
union all select 3,33,'2013-4-12'
union all select 4,24,'2013-4-22'
union all select 5,6,'2013-4-18'
select memberId,MAX(CreateTime) as CreateTime
from
(
select * from test1
union all select * from test2
)t
group by memberId
/*
memberIdCreateTime
52013-04-08
62013-04-18
222013-04-12
232013-04-06
242013-04-22
332013-04-12
*/
create table test1
(Id int,memberId int,CreateTime varchar(16))
create table test2
(Id int,memberId int,CreateTime varchar(16))
insert into test1
select 1, 22, '2013-4-9' union all
select 2, 23, '2013-4-6' union all
select 3, 24, '2013-4-2' union all
select 4, 22, '2013-4-11' union all
select 5, 6, '2013-4-9'
insert into test2
select 1, 22, '2013-4-12' union all
select 2, 5, '2013-4-8' union all
select 3, 33, '2013-4-12' union all
select 4, 24, '2013-4-22' union all
select 5, 6, '2013-4-18'
with t as
(select Id,memberId,CreateTime,
row_number() over(partition by memberId order by CreateTime desc) 'rn'
from
(select Id,memberId,cast(CreateTime as date) 'CreateTime' from test1
union all
select Id,memberId,cast(CreateTime as date) 'CreateTime' from test2) a
)
select memberId,CreateTime
from t where rn=1
/*
memberId CreateTime
----------- ----------
5 2013-04-08
6 2013-04-18
22 2013-04-12
23 2013-04-06
24 2013-04-22
33 2013-04-12
(6 row(s) affected)
*/
select memberId,MAX(CreateTime)
from
(
select * from test1
union
select * from test2
)a group by a.memberId
create table test1
(Id int,memberId int,CreateTime varchar(16))
create table test2
(Id int,memberId int,CreateTime varchar(16))
insert into test1
select 1, 22, '2013-4-9' union all
select 2, 23, '2013-4-6' union all
select 3, 24, '2013-4-2' union all
select 4, 22, '2013-4-11' union all
select 5, 6, '2013-4-9'
insert into test2
select 1, 22, '2013-4-12' union all
select 2, 5, '2013-4-8' union all
select 3, 33, '2013-4-12' union all
select 4, 24, '2013-4-22' union all
select 5, 6, '2013-4-18'
with t as
(select memberId,cast(CreateTime as date) 'CreateTime' from test1
union all
select memberId,cast(CreateTime as date) 'CreateTime' from test2
)
select * from t a
where not exists(select 1 from t b
where b.memberId=a.memberId and b.CreateTime>a.CreateTime)
/*
memberId CreateTime
----------- ----------
23 2013-04-06
22 2013-04-12
5 2013-04-08
33 2013-04-12
24 2013-04-22
6 2013-04-18
(6 row(s) affected)
*/
