注册 登录
编程论坛 SQL Server论坛

求一sql语句

fqbnet2008 发布于 2007-09-27 11:07, 661 次点击

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语句老出错儿,请大家帮我指点一下,或者有好的贴出来看看。

2 回复
#2
purana2007-09-27 14:03

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

#3
心中有剑2007-09-27 16:00
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编辑过]

1