select b.用户名,sum(a.金额/c.用户数) as 金额
from 表A a
inner join 表B b on a.订单号=b.订单号
left join (select 订单号,count(*) as 用户数 from 表B group by 订单号) c on a.订单号=b.订单号
group by b.用户名
from 表A a
inner join 表B b on a.订单号=b.订单号
left join (select 订单号,count(*) as 用户数 from 表B group by 订单号) c on a.订单号=b.订单号
group by b.用户名
select b.用户名,a.金额/(select count(*) from 表B where 订单号=a.订单号) as 金额
from 表B as b
left join 表A as a on a.订单号=b.订单号
) as t group by t.用户名
create table #a(orderid varchar(10),money int)
insert into #a select 200800001,2000
insert into #a select 200800002,1000
create table #b(Userid varchar(10),orderid varchar(10))
insert into #b select 'a',200800001
insert into #b select 'b',200800001
insert into #b select 'c',200800002
insert into #b select 'd',200800001
insert into #b select 'a',200800002select userid,sum(money) as money
from
(
select b.userid,a.orderid,money=a.money/(select count(1) from #b where b.orderid=orderid) from #a a,#b b
where a.orderid=b.orderid
)
aa
group by userid/*
userid money
---------- -----------
a 1166
b 666
c 500
d 666(4 行受影响)*/
insert into TA values('200800001' , 2000)
insert into TA values('200800002' , 1000)
create table TB(用户名 varchar(10) , 订单号 varchar(20))
insert into TB values('a' , '200800001')
insert into TB values('b' , '200800001')
insert into TB values('c' , '200800002')
insert into TB values('d' , '200800001')
insert into TB values('a' , '200800002')
goselect 用户名 , sum(总金额) 总金额 from
(
select m.用户名 , 总金额 = (select 金额 from TA where 订单号 = m.订单号) * 1.0 / (select count(1) from TB where 订单号 = m.订单号) from TB m
) t
group by 用户名drop table TA , TB/*
用户名 总金额
---------- ----------------------------------------
a 1166.666666666666
b 666.666666666666
c 500.000000000000
d 666.666666666666(所影响的行数为 4 行)
*/