select a.id , 'Points'=case when b.Points is null then '' else b.Points end , 'Gift'=case when c.Gift is null then '' else c.Gift endfrom aleft join b on a.id=b.id left join c on b.id=c.id order by a.id
select a.id,b.points,c.gift from A a left join B b on a.id=b.id left join C c on a.id=c.id order by a.id 可以得到输出格式为 id points Gift 1 10 pen 1 20 pencil 1 30 1 40 2 10 box 2 60 pencil 2 box 3 60 flower 3 55 knife 3 15 3 12 3 13 要得到楼主的格式可以通过应用程序来实现
select id=case when not exists(select * from a where id=a.id and points<a.points) then a.id end, a.points,c.gift from A a left join B b on a.id=b.id left join C c on a.id=c.id
hi everybody:首先谢谢啦,按照你们的方式,出来的是Table B and C的笛卡尔积,可是我只是需要B 和 C中需要与A中某个人的真实的纪录数
'Points'=case when b.Points is null then '' else b.Points end ,
'Gift'=case when c.Gift is null then '' else c.Gift endfrom aleft join b on a.id=b.id
left join c on b.id=c.id
order by a.id
from A a left join B b on a.id=b.id
left join C c on a.id=c.id
order by a.id
可以得到输出格式为
id points Gift
1 10 pen
1 20 pencil
1 30
1 40
2 10 box
2 60 pencil
2 box
3 60 flower
3 55 knife
3 15
3 12
3 13
要得到楼主的格式可以通过应用程序来实现
a.points,c.gift
from A a left join B b on a.id=b.id
left join C c on a.id=c.id