超难度sql 语句
表A
cbh sl
1 2
2 3
1 2
2 3
表B
cbh name
1 张
2 王
3 李
4 赵
要得出这样的结果.
表C
cbh sl
1 4
2 6
3 0
4 0
select A.cbh,sum(a.sl) as sl
from a
group by cbh
加上表A在表B里没有的记录;sl以0表示
问题点数：100、回复次数：7Top
1 楼CHENGXB（阿困）回复于 2002-08-25 00:28:10 得分 0
看看这样行不行：
select A.cbh,sum(a.sl) as sl from a group by cbh
union
select cbh,0 as sl from b where cbh not in (select distinct cbh from a)Top
2 楼chinalian（连长）回复于 2002-08-25 00:45:48 得分 10
select cbh, sum(A.s1) as s1 from A, B where A.cbh = B.cbh
group by cbhTop
3 楼jadesun（裤衩）回复于 2002-08-25 01:00:25 得分 10
再加DISTINCT消除重复项Top
4 楼Yang_（扬帆破浪）回复于 2002-09-04 19:56:07 得分 40
select b.cbh,isnull(sum(a.sl),0) as sl
from b left join a
on b.cbh=a.cbh
group by b.cbh
Top
5 楼erickleung（）回复于 2002-09-04 20:09:14 得分 40
select b.cbh,sum(a.sl) as sl
from a
outer join b on a.cbh = b.cbh
group by b.cbh
Top
6 楼My_first（小@_@小）回复于 2002-09-04 20:10:31 得分 0
to erickleung() 这两种连接操作的结果应是相同的吧！
from b left join a
on b.cbh=a.cbh
from a
outer join b on a.cbh = b.cbhTop
7 楼freshboy0913（红绿灯）回复于 2002-09-04 20:46:00 得分 0
select A.cbh,sum(A.sl) as sl from A group by cbh into C
insert into C values
(select cbh from B where B.cbh not in select A.cbh from A, 0)
可能不是最有效率的但我想应该能实现你的功能吧Top
