27,579
社区成员
发帖
与我相关
我的任务
分享SQLSERVER用CTE递归统计各节点人数问题
我有两张表，一张Employee，记录员工信息，包含字段（EId,Name,Sex,Depart），另一张表Orgnazation，记录组织树信息，包含字段（Id,Name,ParentId），想实现的功能是，利用一个存储过程，统计出各组织的总人数、男性人数以及女性人数，员工表测试数据：
EId Name Sex Depart
001 臧三 男 103
002 雄起 男 103
003 冠益乳 女 103
004 但是 男 104
005 热风 男 104
006 裤脚 男 105
007 是多少 女 105
008 出纳 女 106
009 删除 男 107
010 但是 男 107
组织表测试数据：
Id OrgName ParentId
100 M公司 NULL
101 管理中心 100
102 业务中心 100
103 财务部 100
104 人事部 101
105 技术部 101
106 销售1部 102
107 销售2部 102
要实现的效果如下：
组织ID 组织名 上级组织 总人数 男性人数 女性人数
100 M公司 NULL 10 7 3
101 管理中心 100 4 3 1
102 业务中心 100 3 2 1
103 财务部 100 3 2 1
104 人事部 101 2 2 0
105 技术部 101 2 1 1
106 销售1部 102 1 0 1
107 销售2部 102 2 2 0
想用CTE公用表表达式递归查询统计，奈何查出来的数据错误，想请教大神们，怎么实现这个统计
我写的统计SQL如下：
with empCTE(EId,Depart,Sex) as (
select EId,Depart,Sex from Employee
union all
select ee.EId,o.ParentId,ee.Sex from Orgnazation o join Employee ee on o.Id=ee.Depart
join empCTE emp
on o.Id=emp.Depart where o.ParentId is not null
)
select og.Id as 组织ID,og.OrgName as组织名,Og.ParentId as 上级组织,
empSum.EmployeeSum as 总人数,empSum.MaleSum as 男性人数,empSum.FemaleSum as 女性人数
from Orgnazation og left join (select empCTE.Depart as OrgId,COUNT(*) as EmployeeSum
,SUM(case when empCTE.Sex='男' then 1 else 0 end) as MaleSum,
SUM(case when empCTE.Sex='女' then 1 else 0 end) as FemaleSum
from empCTE
left join Orgnazation oo on oo.Id=empCTE.Depart group by empCTE.Depart) empSum on
og.Id=empSum.OrgId
请大神们指点迷津，告诉我怎么改这段SQL或者给提供个能正确统计的SQL，感激不尽。
EId Name Sex Depart
001 臧三 男 103
002 雄起 男 103
003 冠益乳 女 103
004 但是 男 104
005 热风 男 104
006 裤脚 男 105
007 是多少 女 105
008 出纳 女 106
009 删除 男 107
010 但是 男 107
组织表测试数据：
Id OrgName ParentId
100 M公司 NULL
101 管理中心 100
102 业务中心 100
103 财务部 100
104 人事部 101
105 技术部 101
106 销售1部 102
107 销售2部 102
要实现的效果如下：
组织ID 组织名 上级组织 总人数 男性人数 女性人数
100 M公司 NULL 10 7 3
101 管理中心 100 4 3 1
102 业务中心 100 3 2 1
103 财务部 100 3 2 1
104 人事部 101 2 2 0
105 技术部 101 2 1 1
106 销售1部 102 1 0 1
107 销售2部 102 2 2 0
想用CTE公用表表达式递归查询统计，奈何查出来的数据错误，想请教大神们，怎么实现这个统计
我写的统计SQL如下：
with empCTE(EId,Depart,Sex) as (
select EId,Depart,Sex from Employee
union all
select ee.EId,o.ParentId,ee.Sex from Orgnazation o join Employee ee on o.Id=ee.Depart
join empCTE emp
on o.Id=emp.Depart where o.ParentId is not null
)
select og.Id as 组织ID,og.OrgName as组织名,Og.ParentId as 上级组织,
empSum.EmployeeSum as 总人数,empSum.MaleSum as 男性人数,empSum.FemaleSum as 女性人数
from Orgnazation og left join (select empCTE.Depart as OrgId,COUNT(*) as EmployeeSum
,SUM(case when empCTE.Sex='男' then 1 else 0 end) as MaleSum,
SUM(case when empCTE.Sex='女' then 1 else 0 end) as FemaleSum
from empCTE
left join Orgnazation oo on oo.Id=empCTE.Depart group by empCTE.Depart) empSum on
og.Id=empSum.OrgId
请大神们指点迷津，告诉我怎么改这段SQL或者给提供个能正确统计的SQL，感激不尽。
...全文
请发表友善的回复…
发表回复
qq_32662007 2018-11-14
- 打赏
- 举报
记得结贴啊
[/quote]
马上结,haierhaierhaier
[/quote]
马上结,haierhaierhaier
二月十六 2018-11-14
- 打赏
- 举报
记得结贴啊
qq_32662007 2018-11-14
- 打赏
- 举报
谢谢大神，问题解决了～～～
qq_32662007 2018-11-13
- 打赏
- 举报
嗯，输入开始月份、结束月份，查出所有组织每个月的统计数据。。（不好意思）[/quote]
执行存储过程 exec Pro_Test4 '1998-01','1998-12'
查询效果：
ym orgId org total malecount femalecount
1998-01 100 M公司 7 4 3
1998-01 101 管理中心 3 2 1
1998-01 102 业务中心 2 1 1
1998-01 103 财务部 2 1 1
1998-01 104 人事部 1 1 0
1998-01 105 技术部 2 1 1
1998-01 106 销售1部 1 0 1
1998-01 107 销售2部 1 1 0
1998-02 100 M公司 7 4 3
1998-02 101 管理中心 3 2 1
1998-02 102 业务中心 2 1 1
1998-02 103 财务部 2 1 1
1998-02 104 人事部 1 1 0
1998-02 105 技术部 2 1 1
1998-02 106 销售1部 1 0 1
1998-02 107 销售2部 1 1 0
1998-03 100 M公司 7 4 3
1998-03 101 管理中心 3 2 1
1998-03 102 业务中心 2 1 1
1998-03 103 财务部 2 1 1
1998-03 104 人事部 1 1 0
1998-03 105 技术部 2 1 1
1998-03 106 销售1部 1 0 1
1998-03 107 销售2部 1 1 0
1998-04 100 M公司 7 4 3
1998-04 101 管理中心 3 2 1
1998-04 102 业务中心 2 1 1
1998-04 103 财务部 2 1 1
1998-04 104 人事部 1 1 0
1998-04 105 技术部 2 1 1
1998-04 106 销售1部 1 0 1
1998-04 107 销售2部 1 1 0
1998-05 100 M公司 6 3 3
1998-05 101 管理中心 2 1 1
1998-05 102 业务中心 2 1 1
1998-05 103 财务部 2 1 1
1998-05 105 技术部 2 1 1
1998-05 106 销售1部 1 0 1
1998-05 107 销售2部 1 1 0
1998-06 100 M公司 6 3 3
1998-06 101 管理中心 2 1 1
1998-06 102 业务中心 2 1 1
1998-06 103 财务部 2 1 1
1998-06 105 技术部 2 1 1
1998-06 106 销售1部 1 0 1
1998-06 107 销售2部 1 1 0
1998-07 100 M公司 6 3 3
1998-07 101 管理中心 2 1 1
1998-07 102 业务中心 2 1 1
1998-07 103 财务部 2 1 1
1998-07 105 技术部 2 1 1
1998-07 106 销售1部 1 0 1
1998-07 107 销售2部 1 1 0
1998-08 100 M公司 6 3 3
1998-08 101 管理中心 2 1 1
1998-08 102 业务中心 2 1 1
1998-08 103 财务部 2 1 1
1998-08 105 技术部 2 1 1
1998-08 106 销售1部 1 0 1
1998-08 107 销售2部 1 1 0
1998-09 100 M公司 6 3 3
1998-09 101 管理中心 2 1 1
1998-09 102 业务中心 2 1 1
1998-09 103 财务部 2 1 1
1998-09 105 技术部 2 1 1
1998-09 106 销售1部 1 0 1
1998-09 107 销售2部 1 1 0
1998-10 100 M公司 6 3 3
1998-10 101 管理中心 2 1 1
1998-10 102 业务中心 2 1 1
1998-10 103 财务部 2 1 1
1998-10 105 技术部 2 1 1
1998-10 106 销售1部 1 0 1
1998-10 107 销售2部 1 1 0
1998-11 100 M公司 6 3 3
1998-11 101 管理中心 2 1 1
1998-11 102 业务中心 2 1 1
1998-11 103 财务部 2 1 1
1998-11 105 技术部 2 1 1
1998-11 106 销售1部 1 0 1
1998-11 107 销售2部 1 1 0
1998-12 100 M公司 6 3 3
1998-12 101 管理中心 2 1 1
1998-12 102 业务中心 2 1 1
1998-12 103 财务部 2 1 1
1998-12 105 技术部 2 1 1
1998-12 106 销售1部 1 0 1
1998-12 107 销售2部 1 1 0
qq_32662007 2018-11-13
- 打赏
- 举报
嗯，输入开始月份、结束月份，查出所有组织每个月的统计数据。。（不好意思）
二月十六 2018-11-13
- 打赏
- 举报
到底想要哪种效果，能整明白不？
qq_32662007 2018-11-13
- 打赏
- 举报
简单点说就是要查出“某个月”“所有组织”的在职人员情况，想要的效果如下：
Ym OID Name Pid 总人数 男性人数 女性人数
1990-01 100 M公司 NULL 10 7 3
1990-01 101 管理中心 100 4 3 1
1990-01 102 业务中心 100 3 2 1
1990-01 103 财务部 100 3 2 1
1990-01 104 人事部 101 2 2 0
1990-01 105 技术部 101 2 1 1
1990-01 106 销售1部 102 1 0 1
1990-01 107 销售2部 102 2 2 0
qq_32662007 2018-11-13
- 打赏
- 举报
这样的是不对的，不是统计某个月份的人数，而是连续几个月各个月的人数，麻烦大神了
qq_32662007 2018-11-13
- 打赏
- 举报
想要这样的效果，可以选择组织，输入开始时间和结束时间，得出该组织开始到结束时间每个月的在职人数，在职男性人数和在职女性人数：
执行存储过程：exec Pro_Test4 '1998-01','1999-01','100'
查询效果：
ym orgId org total malecount femalecount
1998-01 100 M公司 7 4 3
1998-02 100 M公司 7 4 3
1998-03 100 M公司 7 4 3
1998-04 100 M公司 7 4 3
1998-05 100 M公司 6 3 3
1998-06 100 M公司 6 3 3
1998-07 100 M公司 6 3 3
1998-08 100 M公司 6 3 3
1998-09 100 M公司 6 3 3
1998-10 100 M公司 6 3 3
1998-11 100 M公司 6 3 3
1998-12 100 M公司 6 3 3
1999-01 100 M公司 6 3 3
二月十六 2018-11-13
- 打赏
- 举报
加一个where条件：
如果不对，请楼主给出具体查询条件和想要的结果，再给你写。
;WITH cte AS (
Select Orgnazation.ID,Orgnazation.ID AS OID,Orgnazation.ParentId AS Pid,OrgName AS Name from Orgnazation
UNION ALL
SELECT Orgnazation.ID,OID,cte.Pid,cte.Name FROM dbo.Orgnazation JOIN cte ON cte.ID = Orgnazation.ParentId
)
SELECT
cte.OID,
cte.Name,
cte.Pid,
COUNT(1) AS 总人数,
SUM( CASE
WHEN Sex = '男'
THEN 1
ELSE
0
END
) AS 男性人数,
SUM( CASE
WHEN Sex = '女'
THEN 1
ELSE
0
END
) AS 女性人数
FROM
cte
JOIN
Employee
ON cte.Id = Depart
WHERE IDate >='1990-01-01' AND [Odate] <= '1999-01-01' --这里加上条件就行了
GROUP BY
cte.OID,
cte.Name,
cte.Pid 如果不对，请楼主给出具体查询条件和想要的结果，再给你写。
qq_32662007 2018-11-13
- 打赏
- 举报
大神，这样得到的数据是对的，但是，我现实的情况还要考虑Employee入职和离职时间问题（Employee表上有入职时间和离职时间字段），要那种输入开始月份，结束月份得到期间每个月的统计列表那种，我把你给的代码放进存储过程，统计出来的结果是错误的。
Employee表测试数据如下：
--测试数据
if not object_id(N'Employee') is null
drop table Employee
Go
Create table Employee([EId] nvarchar(23),[Name] nvarchar(23),[Sex] nvarchar(21),[Depart] int,[Idate] date,[Odate] date)
Insert Employee
select N'001',N'臧三',N'男',103,'1998-01-01','2000-03-01' union all
select N'002',N'雄起',N'男',103,'1999-04-01','2000-04-01' union all
select N'003',N'冠益乳',N'女',103,'1997-10-01',NULL union all
select N'004',N'但是',N'男',104,'2000-01-01',NULl union all
select N'005',N'热风',N'男',104,'1990-03-01','1998-05-01' union all
select N'006',N'裤脚',N'男',105,'1995-03-01',NULl union all
select N'007',N'是多少',N'女',105,'1997-05-01','1999-11-01' union all
select N'008',N'出纳',N'女',106,'1994-05-01','2001-09-01' union all
select N'009',N'删除',N'男',107,'1996-03-01','1999-06-01' union all
select N'010',N'但是',N'男',107,'1999-11-01',NULL
GO
存储过程现在的代码如下：
ALTER PROCEDURE [dbo].[Pro_Test4]
@start char(7)='', --查询开始月份
@end char(7)='', --查询结束月份
@dep int=0 --查询组织
AS
BEGIN
create table #Result(ym char(7),org varchar(50),total int,malecount int,femalecount int)
while(@start<=@end)
begin
--统计月份在职员工
select qq.EId,qq.Depart,qq.Idate,qq.Odate,qq.Sex into #emp from Employee qq
inner join Employee ttt on ttt.EId=qq.EId and ttt.Idate <DATEADD(MM,1,@start+'-01')
where qq.Odate>=DATEADD(MM,1,@start+'-01') or qq.Odate is null;
WITH cte AS (
Select Orgnazation.ID,Orgnazation.ID AS OID,Orgnazation.ParentId AS Pid,OrgName AS Name from Orgnazation
UNION ALL
SELECT Orgnazation.ID,OID,cte.Pid,cte.Name FROM dbo.Orgnazation JOIN cte ON cte.ID = Orgnazation.ParentId
)
SELECT
@start as Ym,
cte.OID,
cte.Name,
cte.Pid,
COUNT(1) AS 总人数,
SUM( CASE
WHEN Sex = '男'
THEN 1
ELSE
0
END
) AS 男性人数,
SUM( CASE
WHEN Sex = '女'
THEN 1
ELSE
0
END
) AS 女性人数 into #ttt
FROM
cte
right JOIN
#emp
ON cte.Id = #emp.Depart
where cte.OID=@dep
GROUP BY
cte.OID,
cte.Name,
cte.Pid
OPTION(MAXRECURSION 10)
insert into #Result select v.Ym,v.Name,v.总人数,v.男性人数,v.女性人数 from #ttt v
set @start=CONVERT(char(7),DATEADD(mm,1,@start+'-01'),120)
drop table #emp
drop table #ttt
end
select * from #Result
drop table #Result
END
qq_32662007 2018-11-13
- 打赏
- 举报
大神，这样得到的数据是对的，但是，我现实的情况还要考虑Employee入职和离职时间问题（Employee表上有入职时间和离职时间字段），要那种输入开始月份，结束月份得到期间每个月的统计列表那种，我把你给的代码放进存储过程，统计出来的结果是错误的。
Employee表测试数据如下：
--测试数据
if not object_id(N'Employee') is null
drop table Employee
Go
Create table Employee([EId] nvarchar(23),[Name] nvarchar(23),[Sex] nvarchar(21),[Depart] int,[Idate] date,[Odate] date)
Insert Employee
select N'001',N'臧三',N'男',103,'1998-01-01','2000-03-01' union all
select N'002',N'雄起',N'男',103,'1999-04-01','2000-04-01' union all
select N'003',N'冠益乳',N'女',103,'1997-10-01',NULL union all
select N'004',N'但是',N'男',104,'2000-01-01',NULl union all
select N'005',N'热风',N'男',104,'1990-03-01','1998-05-01' union all
select N'006',N'裤脚',N'男',105,'1995-03-01',NULl union all
select N'007',N'是多少',N'女',105,'1997-05-01','1999-11-01' union all
select N'008',N'出纳',N'女',106,'1994-05-01','2001-09-01' union all
select N'009',N'删除',N'男',107,'1996-03-01','1999-06-01' union all
select N'010',N'但是',N'男',107,'1999-11-01',NULL
GO
存储过程现在的代码如下：
ALTER PROCEDURE [dbo].[Pro_Test4]
@start char(7)='', --查询开始月份
@end char(7)='', --查询结束月份
@dep int=0 --查询组织
AS
BEGIN
create table #Result(ym char(7),org varchar(50),total int,malecount int,femalecount int)
while(@start<=@end)
begin
--统计月份在职员工
select qq.EId,qq.Depart,qq.Idate,qq.Odate,qq.Sex into #emp from Employee qq
inner join Employee ttt on ttt.EId=qq.EId and ttt.Idate <DATEADD(MM,1,@start+'-01')
where qq.Odate>=DATEADD(MM,1,@start+'-01') or qq.Odate is null;
WITH cte AS (
Select Orgnazation.ID,Orgnazation.ID AS OID,Orgnazation.ParentId AS Pid,OrgName AS Name from Orgnazation
UNION ALL
SELECT Orgnazation.ID,OID,cte.Pid,cte.Name FROM dbo.Orgnazation JOIN cte ON cte.ID = Orgnazation.ParentId
)
SELECT
@start as Ym,
cte.OID,
cte.Name,
cte.Pid,
COUNT(1) AS 总人数,
SUM( CASE
WHEN Sex = '男'
THEN 1
ELSE
0
END
) AS 男性人数,
SUM( CASE
WHEN Sex = '女'
THEN 1
ELSE
0
END
) AS 女性人数 into #ttt
FROM
cte
right JOIN
#emp
ON cte.Id = #emp.Depart
where cte.OID=@dep
GROUP BY
cte.OID,
cte.Name,
cte.Pid
OPTION(MAXRECURSION 10)
insert into #Result select v.Ym,v.Name,v.总人数,v.男性人数,v.女性人数 from #ttt v
set @start=CONVERT(char(7),DATEADD(mm,1,@start+'-01'),120)
drop table #emp
drop table #ttt
end
select * from #Result
drop table #Result
END
Employee表测试数据如下：
--测试数据
if not object_id(N'Employee') is null
drop table Employee
Go
Create table Employee([EId] nvarchar(23),[Name] nvarchar(23),[Sex] nvarchar(21),[Depart] int,[Idate] date,[Odate] date)
Insert Employee
select N'001',N'臧三',N'男',103,'1998-01-01','2000-03-01' union all
select N'002',N'雄起',N'男',103,'1999-04-01','2000-04-01' union all
select N'003',N'冠益乳',N'女',103,'1997-10-01',NULL union all
select N'004',N'但是',N'男',104,'2000-01-01',NULl union all
select N'005',N'热风',N'男',104,'1990-03-01','1998-05-01' union all
select N'006',N'裤脚',N'男',105,'1995-03-01',NULl union all
select N'007',N'是多少',N'女',105,'1997-05-01','1999-11-01' union all
select N'008',N'出纳',N'女',106,'1994-05-01','2001-09-01' union all
select N'009',N'删除',N'男',107,'1996-03-01','1999-06-01' union all
select N'010',N'但是',N'男',107,'1999-11-01',NULL
GO
存储过程现在的代码如下：
ALTER PROCEDURE [dbo].[Pro_Test4]
@start char(7)='', --查询开始月份
@end char(7)='', --查询结束月份
@dep int=0 --查询组织
AS
BEGIN
create table #Result(ym char(7),org varchar(50),total int,malecount int,femalecount int)
while(@start<=@end)
begin
--统计月份在职员工
select qq.EId,qq.Depart,qq.Idate,qq.Odate,qq.Sex into #emp from Employee qq
inner join Employee ttt on ttt.EId=qq.EId and ttt.Idate <DATEADD(MM,1,@start+'-01')
where qq.Odate>=DATEADD(MM,1,@start+'-01') or qq.Odate is null;
WITH cte AS (
Select Orgnazation.ID,Orgnazation.ID AS OID,Orgnazation.ParentId AS Pid,OrgName AS Name from Orgnazation
UNION ALL
SELECT Orgnazation.ID,OID,cte.Pid,cte.Name FROM dbo.Orgnazation JOIN cte ON cte.ID = Orgnazation.ParentId
)
SELECT
@start as Ym,
cte.OID,
cte.Name,
cte.Pid,
COUNT(1) AS 总人数,
SUM( CASE
WHEN Sex = '男'
THEN 1
ELSE
0
END
) AS 男性人数,
SUM( CASE
WHEN Sex = '女'
THEN 1
ELSE
0
END
) AS 女性人数 into #ttt
FROM
cte
right JOIN
#emp
ON cte.Id = #emp.Depart
where cte.OID=@dep
GROUP BY
cte.OID,
cte.Name,
cte.Pid
OPTION(MAXRECURSION 10)
insert into #Result select v.Ym,v.Name,v.总人数,v.男性人数,v.女性人数 from #ttt v
set @start=CONVERT(char(7),DATEADD(mm,1,@start+'-01'),120)
drop table #emp
drop table #ttt
end
select * from #Result
drop table #Result
END
二月十六 2018-11-13
- 打赏
- 举报
--测试数据
if not object_id(N'Employee') is null
drop table Employee
Go
Create table Employee([EId] nvarchar(23),[Name] nvarchar(23),[Sex] nvarchar(21),[Depart] int,[Idate] date,[Odate] date)
Insert Employee
select N'001',N'臧三',N'男',103,'1998-01-01','2000-03-01' union all
select N'002',N'雄起',N'男',103,'1999-04-01','2000-04-01' union all
select N'003',N'冠益乳',N'女',103,'1997-10-01',NULL union all
select N'004',N'但是',N'男',104,'2000-01-01',NULl union all
select N'005',N'热风',N'男',104,'1990-03-01','1998-05-01' union all
select N'006',N'裤脚',N'男',105,'1995-03-01',NULl union all
select N'007',N'是多少',N'女',105,'1997-05-01','1999-11-01' union all
select N'008',N'出纳',N'女',106,'1994-05-01','2001-09-01' union all
select N'009',N'删除',N'男',107,'1996-03-01','1999-06-01' union all
select N'010',N'但是',N'男',107,'1999-11-01',NULL
GO
if not object_id(N'Orgnazation') is null
drop table Orgnazation
Go
Create table Orgnazation([Id] int,[OrgName] nvarchar(24),[ParentId] int)
Insert Orgnazation
select 100,N'M公司',null union all
select 101,N'管理中心',100 union all
select 102,N'业务中心',100 union all
select 103,N'财务部',100 union all
select 104,N'人事部',101 union all
select 105,N'技术部',101 union all
select 106,N'销售1部',102 union all
select 107,N'销售2部',102
Go
--测试数据结束
;WITH ctea AS (
SELECT
DATEADD(MONTH, number, CONVERT(DATETIME, '1998-01-01')) AS ym,Id,OrgName,[ParentId]
FROM
master..spt_values ,Orgnazation
WHERE number BETWEEN 0 AND DATEDIFF(MONTH, '1998-01-01', DATEADD(MONTH, 1, '1998-12-01'))-1 AND type='P'
),cteb AS (
SELECT * FROM ctea LEFT JOIN dbo.Employee ON Depart=ctea.Id AND ctea.ym BETWEEN Idate AND ISNULL(Odate,GETDATE())
),ctec AS (
SELECT ym,id AS Orgid,cteb.OrgName,cteb.Sex,id AS lid FROM cteb
UNION ALL
SELECT ctec.ym,ctec.Orgid,ctec.OrgName,cteb.Sex,cteb.Id FROM cteb JOIN ctec ON cteb.ParentId = ctec.lid AND ctec.ym = cteb.ym
)
SELECT
ctec.ym,
ctec.Orgid,
ctec.OrgName,
SUM( CASE
WHEN Sex IS NOT NULL
THEN 1
ELSE
0
END
) AS 总人数,
SUM( CASE
WHEN Sex = '男'
THEN 1
ELSE
0
END
) AS 男性人数,
SUM( CASE
WHEN Sex = '女'
THEN 1
ELSE
0
END
) AS 女性人数
FROM
ctec
GROUP BY
ctec.ym,
ctec.Orgid,
ctec.OrgName
ORDER BY ym OPTION(MAXRECURSION 0)
二月十六 2018-11-12
- 打赏
- 举报
--测试数据
if not object_id(N'Employee') is null
drop table Employee
Go
Create table Employee([EId] nvarchar(23),[Name] nvarchar(23),[Sex] nvarchar(21),[Depart] int)
Insert Employee
select N'001',N'臧三',N'男',103 union all
select N'002',N'雄起',N'男',103 union all
select N'003',N'冠益乳',N'女',103 union all
select N'004',N'但是',N'男',104 union all
select N'005',N'热风',N'男',104 union all
select N'006',N'裤脚',N'男',105 union all
select N'007',N'是多少',N'女',105 union all
select N'008',N'出纳',N'女',106 union all
select N'009',N'删除',N'男',107 union all
select N'010',N'但是',N'男',107
GO
if not object_id(N'Orgnazation') is null
drop table Orgnazation
Go
Create table Orgnazation([Id] int,[OrgName] nvarchar(24),[ParentId] int)
Insert Orgnazation
select 100,N'M公司',null union all
select 101,N'管理中心',100 union all
select 102,N'业务中心',100 union all
select 103,N'财务部',100 union all
select 104,N'人事部',101 union all
select 105,N'技术部',101 union all
select 106,N'销售1部',102 union all
select 107,N'销售2部',102
Go
--测试数据结束
;WITH cte AS (
Select Orgnazation.ID,Orgnazation.ID AS OID,Orgnazation.ParentId AS Pid,OrgName AS Name from Orgnazation
UNION ALL
SELECT Orgnazation.ID,OID,cte.Pid,cte.Name FROM dbo.Orgnazation JOIN cte ON cte.ID = Orgnazation.ParentId
)
SELECT
cte.OID,
cte.Name,
cte.Pid,
COUNT(1) AS 总人数,
SUM( CASE
WHEN Sex = '男'
THEN 1
ELSE
0
END
) AS 男性人数,
SUM( CASE
WHEN Sex = '女'
THEN 1
ELSE
0
END
) AS 女性人数
FROM
cte
JOIN
Employee
ON cte.Id = Depart
GROUP BY
cte.OID,
cte.Name,
cte.Pid
