高分请教一个比较麻烦的统计SQL！！！

Question

问题是这样的，在Dep表中有FatherID字段用于标识父单位，现在想统计表Infor中的记录条数，任务是以母单位为统计单元，也就是统计“人事”的时候，人事、教育、工资的都要统计进去，最终显示的结果在下面有示例。搞了两天没弄出个所以然，请教各位打人啦！表InforID   Infor  DepID
1     XXXX   1
2     XXXX   1
3     XXXX   2
4     XXXX   3
5     XXXX   3
6     XXXX   4
7     XXXX   4
8     XXXX   5
9     XXXX   5
10    XXXX   6
11    XXXX   6
12    XXXX   5表DepDepID  NAME Father
1      厂办    0
2      财务    0
3      人事    0
4      教育    3
5      工资    3
6      后勤    1想统计出如下数据：
字段1 字段2
厂办   4
财务   1
人事   7也就是以fatherID值为0的母单位为基础统计母单位和其包含的子单位在表Infor中的记录条数。

Answer 1

------------------------------------
-- Author:  happyflsytone  
-- Date:2008-10-21 15:32:55
-------------------------------------- Test Data: Infor
IF OBJECT_ID('Infor') IS NOT NULL 
    DROP TABLE Infor
Go
CREATE TABLE Infor(ID INT,Infor NVARCHAR(4),DepID INT)
Go
INSERT INTO Infor
SELECT 1,'XXXX',1 UNION ALL
SELECT 2,'XXXX',1 UNION ALL
SELECT 3,'XXXX',2 UNION ALL
SELECT 4,'XXXX',3 UNION ALL
SELECT 5,'XXXX',3 UNION ALL
SELECT 6,'XXXX',4 UNION ALL
SELECT 7,'XXXX',4 UNION ALL
SELECT 8,'XXXX',5 UNION ALL
SELECT 9,'XXXX',5 UNION ALL
SELECT 10,'XXXX',6 UNION ALL
SELECT 11,'XXXX',6 UNION ALL
SELECT 12,'XXXX',5 
GO
-- Test Data: Dep
IF OBJECT_ID('Dep') IS NOT NULL 
    DROP TABLE Dep
Go
CREATE TABLE Dep(DepID INT,NAME NVARCHAR(2),Father NVARCHAR(1))
Go
INSERT INTO Dep
SELECT 1,'厂办','0' UNION ALL
SELECT 2,'财务','0' UNION ALL
SELECT 3,'人事','0' UNION ALL
SELECT 4,'教育','3' UNION ALL
SELECT 5,'工资','3' UNION ALL
SELECT 6,'后勤','1' 
GO
;with cte
as
(select * ,depid as s ,NAME as n from dep where father = '0'
 union all
 select a.* ,cte.depid ,cte.name from dep a,cte where a.father = cte.depid)
select n,count(1) 
from cte t,Infor i
where t.depid = i.depid
group by n
--Start
--Result:
/*
n    
---- -----------
财务   1
厂办   4
人事   7(3 行受影响)
*/
--End 

Answer 2

--> liangCK小梁 于2008-10-21
--> 生成测试数据: #Infor
IF OBJECT_ID('tempdb.dbo.#Infor') IS NOT NULL DROP TABLE #Infor
CREATE TABLE #Infor (ID INT,Infor VARCHAR(4),DepID INT)
INSERT INTO #Infor
SELECT 1,'XXXX',1 UNION ALL
SELECT 2,'XXXX',1 UNION ALL
SELECT 3,'XXXX',2 UNION ALL
SELECT 4,'XXXX',3 UNION ALL
SELECT 5,'XXXX',3 UNION ALL
SELECT 6,'XXXX',4 UNION ALL
SELECT 7,'XXXX',4 UNION ALL
SELECT 8,'XXXX',5 UNION ALL
SELECT 9,'XXXX',5 UNION ALL
SELECT 10,'XXXX',6 UNION ALL
SELECT 11,'XXXX',6 UNION ALL
SELECT 12,'XXXX',5
--> liangCK小梁 于2008-10-21
--> 生成测试数据: #Dep
IF OBJECT_ID('tempdb.dbo.#Dep') IS NOT NULL DROP TABLE #Dep
CREATE TABLE #Dep (DepID INT,NAME VARCHAR(4),Father INT)
INSERT INTO #Dep
SELECT 1,'厂办',0 UNION ALL
SELECT 2,'财务',0 UNION ALL
SELECT 3,'人事',0 UNION ALL
SELECT 4,'教育',3 UNION ALL
SELECT 5,'工资',3 UNION ALL
SELECT 6,'后勤',1--SQL查询如下:;WITH Liang 
AS
(
   SELECT *,FatherName=CAST(NAME+'-' AS NVARCHAR(1000))
   FROM #Dep
   WHERE Father=0
   
   UNION ALL
   
   SELECT d.DepID,d.NAME,d.Father,CAST(l.FatherName+d.NAME+'-' AS NVARCHAR(1000))
   FROM Liang AS l
     JOIN #Dep AS d
   ON l.DepID=d.Father
)
SELECT CAST(LEFT(l.FatherName,CHARINDEX('-',l.FatherName)-1) AS NVARCHAR(30)) Name,
       COUNT(*) 人数
FROM Liang AS l
 LEFT OUTER JOIN #Infor AS i
ON l.DepID=i.DepID
GROUP BY LEFT(l.FatherName,CHARINDEX('-',l.FatherName)-1)
/*
Name                           人数
------------------------------ -----------
财务                             1
厂办                             4
人事                             7(3 行受影响)
*/

Answer 3

CREATE TABLE #A(ID INT,INFOR VARCHAR(10),DEPID VARCHAR(10))INSERT INTO #A SELECT 1,'XXXX',1 
INSERT INTO #A SELECT 2,'XXXX',1 
INSERT INTO #A SELECT 3,'XXXX',2
INSERT INTO #A SELECT 4,'XXXX',3 
INSERT INTO #A SELECT 5,'XXXX',3 
INSERT INTO #A SELECT 6,'XXXX',4
INSERT INTO #A SELECT 7,'XXXX',4
INSERT INTO #A SELECT 8,'XXXX',5
INSERT INTO #A SELECT 9,'XXXX',5
INSERT INTO #A SELECT 10,'XXXX',6 
INSERT INTO #A SELECT 11,'XXXX',6 
INSERT INTO #A SELECT 12,'XXXX',5 
CREATE TABLE #B(DEPID VARCHAR(10),NAME VARCHAR(10),FATHER INT)INSERT INTO #B SELECT 1,'AA',0
INSERT INTO #B SELECT 2,'BB',0
INSERT INTO #B SELECT 3,'CC',0
INSERT INTO #B SELECT 4,'DD',3 
INSERT INTO #B SELECT 5,'EE',3 
INSERT INTO #B SELECT 6,'FF',1
SELECT NAME,SUM(NUM)NUM FROM 
(SELECT (CASE WHEN B.NAME='BB' THEN 'CAIWU'
                       WHEN B.NAME IN ('EE','CC','DD') THEN 'RENSHI' 
                            ELSE  'CHANGBAN' END )NAME,1 NUM
FROM #A A LEFT JOIN #B B ON (A.DEPID=B.DEPID))A
GROUP BY NAMEDROP TABLE #A,#B