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)
insert into tb values('张飞', null, 100)

想得到一下结果：
郭芙蓉 200
佟相玉 150
王强 150
张飞 100

select a.busman ,a.BusMoney =a.BusMoney + case when Exists(select b.BusMoney from tb b where b.BusMan=a.BusMan or b.BusMan2=a.BusMan and b.BusMan2 is not null)
then b.BusMoney/2
else 0
end

from tb a where a.BusMan2 is null

我的这个sql语句老出错儿，请大家帮我指点一下，或者有好的贴出来看看。

你也要說一下結果有什麼規律吧.

select BusMan, Sum(busmoney / mannum) as BusMoney
from (select BusMan,
BusMoney,
case
when BusMan2 is null then
1
else
2
end as ManNum
from tb
union all
select BusMan2 as BusMan2, BusMoney, 2 as ManNum
from tb
where BusMan2 is not null) a
group by BusMan
order by busmoney desc
没有sql没办法做测试,大概就这么个样子了!看看吧!

[此贴子已经被作者于2007-9-27 16:07:25编辑过]