两表联合

[sunhood]

TA

ID  Name 
19  sss
20  vvv

TB

ID  b1  b2 
19  A1  A11
19  B1  B11
20  E1  E11

TC

ID c1  c2
19 C1  C11 
20 D1  D11
20 W1  W11

如何得到如下结果：

TA.ID  TB.b1 TB.b2 TC.c1 Tc.c2
19     A1    A11   C1    C11
19     B2    B11
20     E1    E11   D1    D11
20                 W1    W11

该怎么写sql呢？

[昵称被占用了]

select isnull(t1.id,t2.id) as id
,isnull(t1.name,t2.name) as name
,t1.b1
,t1.b2
,t2.c1
,t2.c2
from (
select a.id,a.name,b.b1,b.b2,
(select count(*) from tb where id=b.id and (b1<b.b1 or b1=b.b1 and b2<=b.b2)) as id1
from ta a,tb b
where a.id=b.id
) as t1 full join
(
select a.id,a.name,b.c1,b.c2,
(select count(*) from tc where id=c.id and (c1<c.c1 or c1=c.c1 and c2<=c.c2)) as id1
from ta a,tc c
where a.id=c.id
) as t2
on t1.id=t2.id
and t1.id1=t2.id1

[昵称被占用了]

原来不要name，那应该可以简单些，直接用tb,tc full join

select isnull(b.id,c.id) as id
,b.b1
,b.b2
,c.c1
,c.c2
from tb b full join
tc c
on b.id=c.id
and (select count(*) from tb where id=b.id and (b1<b.b1 or b1=b.b1 and b2<=b.b2))=
    (select count(*) from tc where id=c.id and (c1<c.c1 or c1=c.c1 and c2<=c.c2))

[linguojin11]

等待高手。。郁闷。。看不懂哦。。哈哈

[中国风]

SQL05/2000?
05用row_number()over(partition by ID order by ID)生成tb,tc生增 然后TA 連接(full join TB、TC)結果集

[sunhood]

是server2000

[sunhood]

没用到ta表？
结果集中如何再加一个字段Ta.Name 呢？？

TA.ID  TA.Name TB.b1 TB.b2 TC.c1 Tc.c2
19       sss    A1    A11   C1    C11
19       sss    B2    B11
20       vvv    E1    E11   D1    D11
20       vvv                W1    W11

[java_303]

select ta.id,tb.b1,tb.b2,tc.c1,tc.c2 from ta,tb,tc