请教一句SQL语句，麻烦朋友门进来看看，总共只有10分了，实在不好意思

[BadGuyCyh]

是这样的，表结构及数据如下
BusMan BusMan2 BusMoney
郭芙蓉 [空] 100
佟相玉 [空] 100
郭芙蓉 佟相玉 100

要求输出如下：

BusMan BusMoney
郭芙蓉 150
佟相玉 150

也就是说，当BusMan2不为空的时候，那么BusMoney的直给BusMan和BusMan2平分

[dawugui]

create table tb(BusMan varchar(10),BusMan2 varchar(10),BusMoney int)
insert into tb values('郭芙蓉', null, 100)
insert into tb values('佟相玉', null, 100)
insert into tb values('郭芙蓉', '佟相玉', 100)
select busman , BusMoney = BusMoney + (select BusMoney from tb where BusMan2 is not null)/2 from tb where BusMan2 is null
drop table tb

/*
busman BusMoney
---------- -----------
郭芙蓉 150
佟相玉 150

（所影响的行数为 2 行）
*/

[LPQ8306586]

select a.busman,a.busmoney + b.busmoney/2 as busmoney
from (select busman,busmoney from table where busman2 is null) a left join (select * from table where busman2 is not null) b on (a.busman = b.busman or a.busman = b.busman2)

[tomyuansir]

to chuifengde(树上的鸟儿) ( ) 信誉：100
你的avg(BusMoney) BusMoney这里是不是有错呢！！
里面没查出 BusMoney 来 怎么avg？

[BadGuyCyh]

谢谢朋友门的热心帮助，非常感谢

[tomyuansir]

乌龟的正确

[chuifengde]

嘿嘿，搞错了

[喝口水]

select BusMan,BusMoney=sum(BusMoney) from
(
select BusMan,BusMoney=case when BusMan2 is null then BusMoney else BusMoney/2 end from tb
union all
select BusMan2,BusMoney/2 from tb where BusMan2 is not null
)a
group by BusMan
这个肯定正确的

[$扫地僧$]

create table tb(BusMan varchar(10),BusMan2 varchar(10),BusMoney int)
insert into tb values('郭芙蓉', null, 100)
insert into tb values('佟相玉', null, 100)
insert into tb values('郭芙蓉', '佟相玉', 100)

select BusMan,
(select sum(BusMoney) from tb)/(select count(1) from tb where BusMan2 is null) as BusMoney
from tb
where BusMan2 is null

[dawugui]

我的不正确,只是针对他这个表的特殊情况来的.

[chuifengde]

select busman,sum(x) busmoney
from(
select busman,busmoney/2 x from @a where busman2<>'[空]'
union all
select busman2,busmoney/2 from @a where busman2<>'[空]'
union all
select busman,busmoney from @a where busman2='[空]'
)aa
group by busman

[dawugui]

--这个正确
create table tb(BusMan varchar(10),BusMan2 varchar(10),BusMoney int)
insert into tb values('郭芙蓉', null, 100)
insert into tb values('佟相玉', null, 100)
insert into tb values('郭芙蓉', '佟相玉', 100)
go

select tb.busman,tb.busmoney+t1.busmoney/t2.cnt busmoney from tb,
(select sum(BusMoney) busmoney from tb where BusMan2 is not null) t1,
(select count(*) cnt from tb where BusMan2 is null) t2
where tb.BusMan2 is null

drop table tb

/*
busman BusMoney
---------- -----------
郭芙蓉 150
佟相玉 150

（所影响的行数为 2 行）
*/

[chuifengde]

declare @a table(BusMan varchar(20), BusMan2 varchar(20),BusMoney int)
insert @a select '郭芙蓉', '[空]', 100
union all select '佟相玉' ,'[空]', 100
union all select '郭芙蓉' ,'佟相玉', 100
union all select '郭芙蓉' ,'老刘', 500

select busman,sum(x) busmoney
from(
select busman,busmoney/2 x from @a where busman2<>'[空]'
union all
select busman2,busmoney/2 from @a where busman2<>'[空]'
union all
select busman,busmoney from @a where busman2='[空]'
)aa
group by busman
--result
/*
busman busmoney
-------------------- -----------
郭芙蓉 400
老刘 250
佟相玉 150

（所影响的行数为 3 行）
*/

[ojuju10]

楼上有很多不完善：
create table tb(BusMan varchar(10),BusMan2 varchar(10),BusMoney int)
insert into tb values('郭芙蓉', null, 100)
insert into tb values('佟相玉', null, 100)
insert into tb values('郭芙蓉', '佟相玉', 100)
insert into tb values('郭芙蓉', '张三', 100)
insert into tb values('张三', null, 100)



select busman,sum(busmoney) busmoney
from (
select busman,sum(busmoney) busmoney from tb
where busman2 is null
group by busman
union
select busman,sum(busmoney)/2 busmoney from tb
where busman2 is not null
group by busman
union
select busman2 as busman ,sum(busmoney)/2 busmoney from tb
where busman2 is not null
group by busman2
) a
group by busman

drop table tb

busman busmoney
---------- -----------
郭芙蓉 100
佟相玉 150
张三 150

(3 行受影响)

[BadGuyCyh]

楼上的错了把
按照楼上的测试数据
郭芙蓉应该是200
佟相玉是150
张三是150
这样才对，可是你输出的郭芙蓉只有100？

[tomyuansir]

to ojuju10(longdchuanren) ( ) 信誉：100
按你的测试数据来的话 楼主要求的结果应该为：

busman busmoney
---------- -----------
郭芙蓉 200
佟相玉 150
张三 150
但是你的结果不对

[rual_true]

更正一下，我的语句中应该是union all ，不是union，这两个是不一样的，ojuju10可以再试试：
select busman,sum(busmoney) busmoney
from (
select busman,sum(busmoney) busmoney from tb
where busman2 is null
group by busman
union all
select busman,sum(busmoney)/2 busmoney from tb
where busman2 is not null
group by busman
union all
select busman2 ,sum(busmoney)/2 busmoney from tb
where busman2 is not null
group by busman2
) a
group by busman

[LYDF4151]

drop table tb
create table tb(BusMan varchar(10),BusMan2 varchar(10),BusMoney int)
insert into tb values('郭芙蓉', null, 100)
insert into tb values('佟相玉', null, 100)
insert into tb values('郭芙蓉', '佟相玉', 100)


select busman ,'allocation'=a.busmoney+(select busmoney from tb b where b.busman2 is not null and (a.busman=b.busman2 or a.busman=b.busman) ) /2
from tb a where busman2 is null

[cxmcxm]

假设表名为A
select busman,sum(busmoney) busmoney
(
select busman,case when busman2 is null then busmoney else busmoney/2 end busmoney from A
union all
select busman2,busmoney/2 from A where busman2 is not null) b
group by busman

[bluebullet]

借用老乌龟的数据

create table tbb(BusMan varchar(10),BusMan2 varchar(10),BusMoney int)
insert into tbb values('郭芙蓉', null, 100)
insert into tbb values('佟相玉', null, 100)
insert into tbb values('郭蓉', null, 100)
insert into tbb values('佟玉', null, 100)
insert into tbb values('郭蓉7', '佟相4玉', 100)
insert into tbb values('郭蓉3', '佟相5玉', 100)
select * from tbb
select busman , BusMoney = BusMoney + (select sum(BusMoney) from tbb where BusMan2 is not null)/(select count(*) from tbb where BusMan2 is null ) from tbb where BusMan2 is null

[xiangyu9999]

select a.busman,busmoney=(a.busmoney+(select busmoney from TB where (busman=a.busman or busman2=a.busman)and busman2 is not null)/2 )
from TB a
where a.busman2 is null