求助一个人员层级结构的团队业绩统计语句

[earthpea]

今天领导要一个数据，但没有太好的思路，请问大家应该怎么来写一下？
一共二张表：
销售记录表：t_sales；人员信息表：t_psn，表结构和模拟数据如下：

CREATE TABLE t_psn
(
sales_no VARCHAR(10),
sales_leader VARCHAR(10),
sales_level VARCHAR(2)
)

CREATE TABLE t_sales
(s_id INT IDENTITY(1,1),
sales_no VARCHAR(10),
s_article VARCHAR(20),
s_money DECIMAL (12,2)
)

insert t_psn (sales_no,sales_leader,sales_level)  values ( '1001','','20')
insert t_psn (sales_no,sales_leader,sales_level)  values ( '1002','','20')
insert t_psn (sales_no,sales_leader,sales_level)  values ( '1003','1001','10')
insert t_psn (sales_no,sales_leader,sales_level)  values ( '1004','1001','10')
insert t_psn (sales_no,sales_leader,sales_level)  values ( '1005','1003','9')
insert t_psn (sales_no,sales_leader,sales_level)  values ( '1006','1002','9')

insert t_sales (sales_no,s_article,s_money)  values ( '1001','AAA',50.00)
insert t_sales (sales_no,s_article,s_money)  values ( '1001','BAA',60.00)
insert t_sales (sales_no,s_article,s_money)  values ( '1002','BAA',10.00)
insert t_sales (sales_no,s_article,s_money)  values ( '1003','BBA',100.00)
insert t_sales (sales_no,s_article,s_money)  values ( '1004','BBA',100.00)
insert t_sales (sales_no,s_article,s_money)  values ( '1005','BBB',90.00)
insert t_sales (sales_no,s_article,s_money)  values ( '1006','BBB',90.00)

现在的要求是：列出所有10级以上人员的团队销售情况包括：
工号、总件数、销售金额
例如这个数据里，1001/1002/1003/1004都要被列出来，1002就是2件，100元，1004就是1件，100元，1001应该是5件，400元。

一个语句出来也行，用临时表一步步也行，或者先将所有10级以上的团队人员放到一张表里再递归也可以，呵呵

我试了半个下午了，好像总有些不太对头的地方，因为在实际的数据中，还要分成一百多个不同的分支机构，每个机构有数十到上百人，每人有十件左右的销售记录，一个机构大约有三至五个层级，头都想晕了～～

[ws_hgo]

create table #EnterPrise
(
  Department nvarchar(50),--部门名称
  ParentDept nvarchar(50),--上级部门
  DepartManage nvarchar(30)--部门经理
)
insert into #EnterPrise select '技术部','总经办','Tom'
insert into #EnterPrise select '商务部','总经办','Jeffry'
insert into #EnterPrise select '商务一部','商务部','ViVi'
insert into #EnterPrise select '商务二部','商务部','Peter'
insert into #EnterPrise select '程序组','技术部','GiGi'
insert into #EnterPrise select '设计组','技术部','yoyo'
insert into #EnterPrise select '专项组','程序组','Yue'
insert into #EnterPrise select '总经办','','Boss'
--查询部门经理是Tom的下面的部门名称
;with hgo as
(
   select *,0 as rank from #EnterPrise where DepartManage='Tom'
   union all
   select h.*,h1.rank+1 from #EnterPrise h join hgo h1 on h.ParentDept=h1.Department
)
select * from hgo
/*
Department           ParentDept                DepartManage      rank
--------------- -------------------- ----------------------- -----------
技术部               总经办                    Tom               0
程序组               技术部                    GiGi              1
设计组               技术部                    yoyo              1
专项组               程序组                    Yue               2
*/
--查询部门经理是GiGi的上级部门名称
;with hgo as
(
   select *,0 as rank from #EnterPrise where DepartManage='GiGi'
   union all
   select h.*,h1.rank+1 from #EnterPrise h join hgo h1 on h.Department=h1.ParentDept
)
select * from hgo
/*
Department               ParentDept          DepartManage    rank
-------------------- ----------------------  -----------  -----------
程序组                   技术部                 GiGi           0
技术部                   总经办                 Tom            1
总经办                                          Boss           2
*/



本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ws_hgo/archive/2010/01/31/5274571.aspx

[earthpea]

谢谢楼上的，我的思路准备这样做：
把10级以上的人员提出来到一张表中，用游标方式来一个个的做？可能会效率很低，但毕竟可以做出来，大家还有更好更快一些的方法吗？

[billpu]

能解决问题就是一个好办法 帮顶

[coleling]

楼主，试一下，看看是不是你想要的：

with cte as
(
	select no=sales_no,sales_no,sales_leader from t_psn where sales_level >= 10
	union all
	select a.no,b.sales_no,b.sales_leader from cte a join t_psn b on a.sales_no = b.sales_leader
)
select 工号=a.no,总件数=count(1),销售金额=sum(s_money) 
from cte a 
join t_sales b on a.sales_no = b.sales_no 
group by a.no

/*
工号         总件数         销售金额
---------- ----------- ---------------------------------------
1001       5           400.00
1002       2           100.00
1003       2           190.00
1004       1           100.00

(4 行受影响)
*/

[王向飞]

已经很典型了，套用1楼的CTE
或者你全取出来，用程序来算，比SQL方便很多

[earthpea]

十分感谢大家的热情帮助，问题已经处理，用的是四楼的方法，不过也要感谢一楼提供了一个很好的思路。

我说一下我的过程：
数据量：业务员约1.5w人，主管级别的人员约有2.4k，服务器为windows 2003 x64/sql server 2005 x64/16C/64G

因为数据今天就要，六点时开始使用一楼的方法
建立临时表1，将所有业务员信息插入；更新临时表1的数据，将每一个业务员的销售情况、上级信息插入；
建立临时表2，将表1中的10级以上人员插入表中；
对表2建立游标，使用1楼的方法将表2中的主管级业务员的所有下属递归出来，统计出数据，插入临时表3
使用表3的数据更新表2的数据，select出表2所有数据，整个过程用时十分钟

刚才看到了四楼的回复，使用四楼的脚本：
建立临时表1，将业务员信息插入，更新表1，将业务员销售情况、上级信息插入
直接使用四楼的脚本，整个过程用时三秒

最后准备使用四楼的方法，但也十分感谢一楼的回复，又让我学到了很多东西。