如何用SQL做业绩汇总
A 介绍 B,C,D ,然后B介绍M,N, C介绍R,P,Q, D没有介绍别人.假如每个人都有2000的营业额,现在我想看每个人的营业额及每个人含下属的营业额之和.这个用SQL要怎么写
A
--B
----M
----N
--C
----R
----P
----Q
--D
人员表:
工号,介绍人工号
A,
B,A
C,A
D,A
M,B
N,B
R,C
P,C
Q,C
业绩表
工号,业绩
A,2000
B,2000
C,2000
D,2000
M,2000
N,2000
R,2000
P,2000
Q,2000
[解决办法]
with tb (a,b)as(
select 'A',null union all
select 'B','A' union all
select 'C','A' union all
select 'D','A' union all
select 'M','B' union all
select 'N','B' union all
select 'R','C' union all
select 'P','C' union all
select 'Q','C'
),
t as (
select A,b=a from tb
union all
select t.a,b=tb.b from t inner join tb on t.b=tb.a
),
t1 as (
select b,COUNT(b)countb from t where b is not null and a!=b
group by b)
select a.a,2000 b,isnull(countb,0)*2000 c from tb a left join t1 b on a.a=b.b
with t as (
select name,jsr=name from dbo.emp1
union all
select t.name,b=emp1.jsr from t inner join emp1 on t.jsr=emp1.name
)
select code,qnt,ISNULL(b,0)*qnt as b from dbo.sale1 a left join (
select jsr,COUNT(jsr)b from t where jsr !='' and name!=jsr group by jsr)b
on a.code=b.jsr
