2张表格联合查询的统计问题

Question

有两张表格
tblA（人员表，字段：ID、人员姓名、所在单位）
ID RName Unit 级别
1 王一 单位1 专职
2 李二 单位2 专职
3 张三 单位3 兼职
4 赵四 单位3 专职
5 刘五 单位4 专职
......tblB（单位表，字段：ID、单位级别、单位名称、上级单位名称）
ID Class Name Upper
1 集团 单位1 无
2 公司 单位2 单位1
3 子公司 单位3 单位2
4 子公司 单位4 单位2
......我现在想用sql查询语句，得到如下的统计表：单位名称 总人数 其中：专职人数 其中：兼职人数
集团
公司1
公司2   
合计统计表说明：集团的统计结果是集团本身登记的人员（不包括公司和子公司数据）；公司统计结果显示的是公司本身加上其下属子公司的结果（点公司1名称，显示其下属子公司的统计表，表头一样）；合计，显示集团所有登记的人员。（Group by貌似，但是晕乎了，谢谢各位大侠了。）

Answer 1

create table tblA
(
  ID int,
  RName nvarchar(10),
  Unit nvarchar(50),
  级别 nvarchar(10)
)insert into tblA
select 1,'王一', '单位1', '专职' union all
select 2, '李二', '单位2', '专职' union all
select 3, '张三', '单位3', '兼职' union all
select 4, '赵四', '单位3', '专职' union all
select 5, '刘五', '单位4', '专职'Create table tblB
(
 ID int,
 Class nvarchar(10),
 Name nvarchar(10),
 [Upper]nvarchar(10)
)
insert into tblB
select 1, '集团', '单位1', '无'  union all
select 2, '公司', '单位2', '单位1'  union all
select 3, '子公司', '单位3', '单位2'  union all
select 4, '子公司', '单位4', '单位2'--为了清楚创建一个视图
create view VwTest
AS
select b.Class ,b.Name,a.RName ,a.级别,b.Upper   from tblA a inner join tblB b on a.Unit =b.Name 
--递归函数
CREATE FUNCTION dbo.fn_getEmployeeCount(@name AS nvarchar(10)) 
RETURNS TABLE 
AS 
RETURN(
  WITH locs(Class,Name,RName,级别,Upper,loclevel)
AS
(
SELECT Class,Name,级别,Upper,RName,0 AS loclevel FROM VwTest
WHERE Name =@name
UNION ALL
SELECT l.Class,l.Name,l.RName,l.级别,l.Upper,loclevel+1 FROM VwTest l INNER JOIN locs p
ON l.Upper=p.Name
) SELECT count(*) EmployeeCount FROM locs

 
)
GOSELECT *
FROM tblB t1
    CROSS APPLY fn_getEmployeeCount(t1.name) t2

Answer 2

修改如下create table tblA
(
  ID int,
  RName nvarchar(10),
  Unit nvarchar(50),
  级别 nvarchar(10)
)insert into tblA
select 1,'王一', '单位1', '专职' union all
select 2, '李二', '单位2', '专职' union all
select 3, '张三', '单位3', '兼职' union all
select 4, '赵四', '单位3', '专职' union all
select 5, '刘五', '单位4', '专职'Create table tblB
(
 ID int,
 Class nvarchar(10),
 Name nvarchar(10),
 [Upper]nvarchar(10)
)
insert into tblB
select 1, '集团', '单位1', '无'  union all
select 2, '公司', '单位2', '单位1'  union all
select 3, '子公司', '单位3', '单位2'  union all
select 4, '子公司', '单位4', '单位2'--为了清楚创建一个视图
create view VwTest
AS
select b.Class ,b.Name,a.RName ,a.级别,b.Upper from tblA a inner join tblB b on a.Unit =b.Name 
--递归函数
CREATE FUNCTION dbo.fn_getEmployeeCount(@name AS nvarchar(10)) 
RETURNS TABLE 
AS 
RETURN(
  WITH locs(Class,Name,RName,级别,Upper,loclevel)
AS
(
SELECT Class,Name,级别,Upper,RName,0 AS loclevel FROM VwTest
WHERE Name =@name
UNION ALL
SELECT l.Class,l.Name,l.RName,l.级别,l.Upper,loclevel+1 FROM VwTest l INNER JOIN locs p
ON l.Upper=p.Name
) SELECT count(*) AS 总人数,sum(case when 级别='专职' then 1 else 0 end) AS 专职人数,sum(case when 级别='兼职' then 1 else 0 end) AS 兼职人数  FROM locs

)
SELECT *
FROM tblB t1
    CROSS APPLY fn_getEmployeeCount(t1.name) t2
    
ID          Class      Name       Upper      总人数         专职人数        兼职人数
----------- ---------- ---------- ---------- ----------- ----------- -----------
1           集团         单位1        无          5           3           1
2           公司         单位2        单位1        4           2           1
3           子公司        单位3        单位2        2           0           0
4           子公司        单位4        单位2        1           0           0(4 行受影响)

Answer 3

IF OBJECT_ID('tblA') IS NOT NULL
DROP TABLE tblA
GOcreate table tblA
(
  ID int,
  RName nvarchar(10),
  Unit nvarchar(50),
  级别 nvarchar(10)
)Goinsert into tblA
select 1,'王一', '单位1', '专职' union all
select 2, '李二', '单位2', '专职' union all
select 3, '张三', '单位3', '兼职' union all
select 4, '赵四', '单位3', '专职' union all
select 5, '刘五', '单位4', '专职'IF OBJECT_ID('tblB') IS NOT NULL
DROP TABLE tblB
GOCreate table tblB
(
 ID int,
 Class nvarchar(10),
 Name nvarchar(10),
 [Upper]nvarchar(10)
)Goinsert into tblB
select 1, '集团', '单位1', '无'  union all
select 2, '公司', '单位2', '单位1'  union all
select 3, '子公司', '单位3', '单位2'  union all
select 4, '子公司', '单位4', '单位2'GoIF OBJECT_ID('VwTest') IS NOT NULL
DROP view VwTest
GO--为了清楚创建一个视图
create view VwTest
AS
select b.Class ,b.Name,a.RName ,a.级别,b.Upper from tblA a inner join tblB b on a.Unit =b.Name GoIF OBJECT_ID('fn_getEmployeeCount') IS NOT NULL
DROP FUNCTION fn_getEmployeeCount
GO--递归函数
CREATE FUNCTION fn_getEmployeeCount(@name AS nvarchar(10)) 
RETURNS TABLE 
AS 
RETURN(
  WITH locs(Class,Name,RName,级别,Upper,loclevel)
AS
(
SELECT Class,Name,级别,Upper,RName,0 AS loclevel FROM VwTest
WHERE Name =@name
UNION ALL
SELECT l.Class,l.Name,l.RName,l.级别,l.Upper,loclevel+1 FROM VwTest l INNER JOIN locs p
ON l.Upper=p.Name
) SELECT count(*) AS 总人数,sum(case when 级别='专职' then 1 else 0 end) AS 专职人数,sum(case when 级别='兼职' then 1 else 0 end) AS 兼职人数  FROM locs

)GOSELECT *
FROM tblB t1
    CROSS APPLY fn_getEmployeeCount(t1.name) t2