如何一主表和两从表中,统计最后的登记日期.

[lbd8848]

表a
uid uname
------------------
001 aaaaa
002 bbbbb
003 ccccc
004 ddddd

表b
uid adddate .....
------------------------
002 2005-09-12 06:12:02
003 2005-09-11 04:09:21
002 2005-09-11 09:09:21
001 2005-09-21 21:21:21
001 2005-09-21 11:11:11
002 2005-09-13 12:09:07

表c
uid adddate ......
-----------------------------
001 2005-09-22 00:09:00
003 2005-08-28 01:01:00
001 2005-09-01 12:02:11

从三表中取出如下的结果
uid uname addate
-----------------------------
001 aaaaa 2005-09-22 00:09:00
002 bbbbb 2005-09-13 12:09:07
003 ccccc 2005-09-11 04:09:21
004 ddddd null
也即取出表A中所有对应uid最后的登记日期

[wgsasd311]

select a.*,
addate=(case when b.adddate is null then c.adddate else b.adddate end)
from 表a a left join 表b b on a.uid=b.uid
left join 表c c on a.uid=c.uid

[vivianfdlpw]

select a.uid
,a.uname
,max(case
when b.adddate>c.adddate then b.adddate
when b.adddate<c.adddate c.adddate
end) as 'adddate'
from a
left join b on a.uid=b.uid
left join c on a.uid=c.uid
group by a.uid,a.uname

[Ivan1982]

select uid,uname,adddate
from(
(select a.uid,a.uname,b.adddate from a,b where a.uid=b.uid)
union all
(select a.uid,a.uname,c.adddate from a,c where a.uid=c.uid)
) c where not exists(select count(*) from b where b.uid=a.uid and b.adddata>a.adddate)
and not exists(select count(*) from c where c.uid=a.uid and c.adddate>a.addddate)

[vivianfdlpw]

select a.uid
,a.uname
,max(case
when b.adddate>c.adddate then b.adddate
when b.adddate<c.adddate then c.adddate
end) as 'adddate'
from a
left join b on a.uid=b.uid
left join c on a.uid=c.uid
group by a.uid,a.uname

[klan]

select a.uid
,a.uname
,max(case
when b.adddate>=c.adddate then b.adddate
when b.adddate<c.adddate then c.adddate
end) as 'adddate'
from a
left join b on a.uid=b.uid
left join c on a.uid=c.uid
group by a.uid,a.uname

[$扫地僧$]

select 表a.* from 表a,(select B_C.uid,max(B_C.adddate) from (select uid, adddate from 表b union select uid, adddate from 表C) B_C group by B_C.uid) T
where 表a.uid*=T.uid

[子陌红尘]

select
a.*,
d.addate
from
a
left join
(select * from b union select * from c) d
on
a.uid = d.uid
where
not exists(select
1
from
(select * from b union select * from c) e
where
e.uid = a.uid and d.addate>e.addate)
order by
a.uid