是这样的,表结构及数据如下
BusMan BusMan2 BusMoney
郭芙蓉 [空] 100
佟相玉 [空] 100
郭芙蓉 佟相玉 100要求输出如下:BusMan BusMoney
郭芙蓉 150
佟相玉 150也就是说,当BusMan2不为空的时候,那么BusMoney的直给BusMan和BusMan2平分
BusMan BusMan2 BusMoney
郭芙蓉 [空] 100
佟相玉 [空] 100
郭芙蓉 佟相玉 100要求输出如下:BusMan BusMoney
郭芙蓉 150
佟相玉 150也就是说,当BusMan2不为空的时候,那么BusMoney的直给BusMan和BusMan2平分
select busman,sum(busmoney) from
(
select busman,sum(busmoney) as busmoney
from bus
where busman2 is null
group by busman
union all
select busman,sum(busmoney/2) as busmoney
from bus
where busman2 is not null
group by busman
select busman2 as busman,sum(busmoney/2) as busmoney
from bus
where busman2 is not null
group by busman2
) group by busman;
from(
select BusMan from [Table]
union all
select BusMan2 from [Table] where BusMan2<>'[空]'
)aa
group by BusMan
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 行)
*/
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)
你的avg(BusMoney) BusMoney这里是不是有错呢!!
里面没查出 BusMoney 来 怎么avg?
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
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
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)
goselect 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 nulldrop table tb/*
busman BusMoney
---------- -----------
郭芙蓉 150
佟相玉 150(所影响的行数为 2 行)
*/
insert @a select '郭芙蓉', '[空]', 100
union all select '佟相玉' ,'[空]', 100
union all select '郭芙蓉' ,'佟相玉', 100
union all select '郭芙蓉' ,'老刘', 500select 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 行)
*/
楼上有很多不完善:
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 busmandrop table tbbusman busmoney
---------- -----------
郭芙蓉 100
佟相玉 150
张三 150(3 行受影响)
按你的测试数据来的话 楼主要求的结果应该为:busman busmoney
---------- -----------
郭芙蓉 200
佟相玉 150
张三 150
但是你的结果不对
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
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
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
from TB a
where a.busman2 is null
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) from(
select busman,busmoney=case when busman2 is null then busmoney else busmoney/2 end from tb
union all
select busman2,busmoney=busmoney/2 from tb where busman2 is not null) a group by busman
当BusMan2中用户包含BusMan1中时,看接下来是怎样平分的,
对BusMan2中包含了BusMan的平分,包含了几次就对被包含的 BusMan平分几次,
而对于没有被包含的BusMan应该是不进行平分才对,
不知我的理解是否是楼主的意思。
declare @T table(BusMan varchar(6),BusMan2 varchar(6),BusMoney int)
insert @T
select '郭芙蓉',null,100 union all
select '佟相玉',null,100 union all
select '郭芙蓉','佟相玉',100select BusMan,BusMoney=sum(BusMoney) from
(
select BusMan,BusMoney=case when BusMan2 is null then BusMoney else BusMoney/2 end from @T
union all
select BusMan2,BusMoney/2 from @T where BusMan2 is not null
) a
group by BusMan/*
BusMan BusMoney
郭芙蓉 150
佟相玉 150
*/
(
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
这个肯定正确的
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