数据如下:
公司主键 公司名称 上级公司主键 员工姓名
1000 集团总部 1000 a1
1001 北京分公司 1000 b1
1001 北京分公司 1000 b2
1002 天津分公司 1000 c1
1002 天津分公司 1000 c2
1003 河北分公司 1000 d1
1003 河北分公司 1000 d2
1004 北京分公司朝阳办事处 1001 e1
1005 北京分公司海淀办事处 1001 f1
1006 天津分公司北城办事处 1002 g1
---------------------------------------------------
希望统计北京、天津、河北以及各自下级公司的人数。
公司主键 公司名称 人数
1001 北京分公司 4
1002 天津分公司 3
1003 河北分公司 2
说明:公司主键在编码上没有规律,下级公司可能会很多
CREATE TABLE corp (
pk_1 varchar2(30),
comp varchar2(30),
fath_pk varchar2(30),
employ varchar2(30)
)
;
insert into corp(
pk_1,comp,fath_pk,employ
)
values( '1000','集团总部','1000','a1');
insert into corp(
pk_1,comp,fath_pk,employ
)
values( '1001','北京分公司','1000','b1');
insert into corp(
pk_1,comp,fath_pk,employ
)
values( '1001','北京分公司','1000','b2');
insert into corp(
pk_1,comp,fath_pk,employ
)
values( '1002','天津分公司','1000','c1');
insert into corp(
pk_1,comp,fath_pk,employ
)
values( '1002','天津分公司','1000','c2');
insert into corp(
pk_1,comp,fath_pk,employ
)
values( '1003','河北分公司','1000','d1');
insert into corp(
pk_1,comp,fath_pk,employ
)
values( '1003','河北分公司','1000','d2');
insert into corp(
pk_1,comp,fath_pk,employ
)
values( '1004','北京分公司朝阳办事处','1001','e1') ;
insert into corp(
pk_1,comp,fath_pk,employ
)
values( '1005','北京分公司朝阳办事处','1001','f1') ;
insert into corp(
pk_1,comp,fath_pk,employ
)
values( '1006','天津分公司北城办事处','1002','g1') ;
公司主键 公司名称 上级公司主键 员工姓名
1000 集团总部 1000 a1
1001 北京分公司 1000 b1
1001 北京分公司 1000 b2
1002 天津分公司 1000 c1
1002 天津分公司 1000 c2
1003 河北分公司 1000 d1
1003 河北分公司 1000 d2
1004 北京分公司朝阳办事处 1001 e1
1005 北京分公司海淀办事处 1001 f1
1006 天津分公司北城办事处 1002 g1
---------------------------------------------------
希望统计北京、天津、河北以及各自下级公司的人数。
公司主键 公司名称 人数
1001 北京分公司 4
1002 天津分公司 3
1003 河北分公司 2
说明:公司主键在编码上没有规律,下级公司可能会很多
CREATE TABLE corp (
pk_1 varchar2(30),
comp varchar2(30),
fath_pk varchar2(30),
employ varchar2(30)
)
;
insert into corp(
pk_1,comp,fath_pk,employ
)
values( '1000','集团总部','1000','a1');
insert into corp(
pk_1,comp,fath_pk,employ
)
values( '1001','北京分公司','1000','b1');
insert into corp(
pk_1,comp,fath_pk,employ
)
values( '1001','北京分公司','1000','b2');
insert into corp(
pk_1,comp,fath_pk,employ
)
values( '1002','天津分公司','1000','c1');
insert into corp(
pk_1,comp,fath_pk,employ
)
values( '1002','天津分公司','1000','c2');
insert into corp(
pk_1,comp,fath_pk,employ
)
values( '1003','河北分公司','1000','d1');
insert into corp(
pk_1,comp,fath_pk,employ
)
values( '1003','河北分公司','1000','d2');
insert into corp(
pk_1,comp,fath_pk,employ
)
values( '1004','北京分公司朝阳办事处','1001','e1') ;
insert into corp(
pk_1,comp,fath_pk,employ
)
values( '1005','北京分公司朝阳办事处','1001','f1') ;
insert into corp(
pk_1,comp,fath_pk,employ
)
values( '1006','天津分公司北城办事处','1002','g1') ;
start with pk_1=c.pk_1
connect by prior pk_1=fath_pk)
from corp c
where c.fath_pk='1000'
and c.pk_1<>'1000'
group by c.pk_1
但我的真实业务系统中,是2张表,一个公司表,一个员工表
而且有些二级公司本身是没有人的,只有下属的三级公司才有,
即有这样的数据:“湖北分公司” 在公司表里有数据,但员工表没有人,公司表和员工表联合查询后,不会出现“湖北分公司”记录
做为它的下级公司 “湖北分公司东湖办事处”下面有人,就会多出
insert into corp(
pk_1,comp,fath_pk,employ
)
values( '1008','湖北分公司东湖办事处','1007','h1') ;
这条数据,这样这个脚本就不适用了
--测试数据
DECLARE @a TABLE(No varchar(10),Name varchar(10))
INSERT @a SELECT '101' ,'现金'
UNION ALL SELECT '102' ,'银行存款'
UNION ALL SELECT '10201','工行'
UNION ALL SELECT '10202','建行'
UNION ALL SELECT '10203','农行'DECLARE @b TABLE(No varchar(10),[Money] money )
INSERT @b SELECT '101' ,100
UNION ALL SELECT '10201',20
UNION ALL SELECT '10202',120--逐级汇总查询
SELECT a.No,a.Name,
[Money]=ISNULL(SUM([Money]),0)
FROM @a a
LEFT JOIN @b b ON b.No LIKE a.No+'%'
GROUP BY a.No,a.Name
ORDER BY a.No
/*--结果
No Name Money
---------------- ----------------- ---------------------
101 现金 100.0000
102 银行存款 140.0000
10201 工行 20.0000
10202 建行 120.0000
10203 农行 .0000
--*/
--测试数据
DECLARE @t TABLE(No varchar(10))
INSERT @t SELECT '1'
UNION ALL SELECT '3'
UNION ALL SELECT '302'
UNION ALL SELECT '305'
UNION ALL SELECT '305001'
UNION ALL SELECT '305005'
UNION ALL SELECT '6'
UNION ALL SELECT '601'--重排编码
UPDATE a SET
No=RIGHT(--重排第一层编码
(SELECT COUNT(DISTINCT No) FROM @t WHERE No<=a.No AND LEN(No)=1),1)
+CASE --重排第二层编码
WHEN LEN(No)>1
THEN RIGHT(100+
(SELECT COUNT(DISTINCT No) FROM @t WHERE No<=a.No AND No LIKE LEFT(a.NO,1)+'__'),2)
ELSE '' END
+CASE --重排第三层编码
WHEN LEN(No)>3
THEN RIGHT(1000+
(SELECT COUNT(DISTINCT No) FROM @t WHERE No<=a.No AND No LIKE LEFT(a.NO,3)+'___'),3)
ELSE '' END
FROM @t a--显示处理结果
SELECT * FROM @t
/*--结果
No
----------
1
2
201
202
202001
202002
3
301
--*/
--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END--显示结果
SELECT SPACE(b.Level*2)+'|--'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
|--烟台市
|--招远市
|--青岛市
|--四会市
|--清远市
|--小分市
--*/
--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'--广度排序显示处理
--生成每个节点的层次数据
DECLARE @t_Level TABLE(ID char(3),Level int)
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END--显示结果
SELECT a.*
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Level,b.ID
/*--结果
ID PID Name
------- --------- ----------
001 NULL 山东省
005 NULL 四会市
002 001 烟台市
003 001 青岛市
006 005 清远市
004 002 招远市
007 006 小分市
--*/
--测试数据
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO--广度搜索排序函数
CREATE FUNCTION f_Sort(@ID char(3)=NULL,@sort int=1)
RETURNS @t_Level TABLE(ID char(3),sort int)
AS
BEGIN
DECLARE tb CURSOR LOCAL
FOR
SELECT ID FROM tb
WHERE PID=@ID
OR(@ID IS NULL AND PID IS NULL)
OPEN TB
FETCH tb INTO @ID
WHILE @@FETCH_STATUS=0
BEGIN
INSERT @t_Level VALUES(@ID,@sort)
SET @sort=@sort+1
IF @@NESTLEVEL<32 --如果递归层数未超过32层(递归最大允许32层)
BEGIN
--递归查找当前节点的子节点
INSERT @t_Level SELECT * FROM f_Sort(@ID,@sort)
SET @sort=@sort+@@ROWCOUNT --排序号加上子节点个数
END
FETCH tb INTO @ID
END
RETURN
END
GO--显示结果
SELECT a.*
FROM tb a,f_Sort(DEFAULT,DEFAULT) b
WHERE a.ID=b.ID
ORDER BY b.sort
/*--结果
ID PID Name
------ --------- ----------
001 NULL 山东省
002 001 烟台市
004 002 招远市
003 001 青岛市
005 NULL 四会市
006 005 清远市
007 006 小分市
--*/
-------------
CREATE TABLE t_department (
code varchar2(30),
upcode varchar2(30),
name varchar2(30));
INSERT INTO t_department (code, upcode, name)
values ('1001', '', 'XX集团股份有限公司');
INSERT INTO t_department (code, upcode, name)
values ('1002', '1001', '北京分公司');
INSERT INTO t_department (code, upcode, name)
values ('1003', '1001', '上海分公司');
INSERT INTO t_department (code, upcode, name)
values ('1004', '1001', '天津分公司');
INSERT INTO t_department (code, upcode, name)
values ('1005', '1001', '集团总裁办公室');
INSERT INTO t_department (code, upcode, name)
values ('1006', '1001', '集团财务部');
INSERT INTO t_department (code, upcode, name)
values ('1007', '1002', '北京分公司办事处');
CREATE TABLE t_employee (
employee varchar2(30),
code varchar2(30),
name varchar2(30));
INSERT INTO t_employee (employee, code, name)
values ('a1', '1003', '张三');
INSERT INTO t_employee (employee, code, name)
values ('a2', '1004', '李四');
INSERT INTO t_employee (employee, code, name)
values ('a3', '1005', '王五');
INSERT INTO t_employee (employee, code, name)
values ('a4', '1006', '赵六');
INSERT INTO t_employee (employee, code, name)
values ('a5', '1007', '赵七');第一个汇总下级单位(中间有一级单位没有人,如北京分公司)的问题已经解决。我联表查询时用右连接(公司表为右),然后再套用上面脚本就能解决。
现在有另外一个问题,“XX集团股份有限公司”的直接下级单位除了有3个分公司外,还有2个(真实数据不止2个)属于总部的职能部门,希望能把他们单独统计。
即上述数据希望统计的人数为:
集团总部 2人
北京分公司 1人
上海分公司 1人
天津分公司 1人
你怎么确定哪个是分公司,哪个需要统计入总部啊?
看名称的?
如果这样的话,我照原来的改了下,估计效率更低了,楼住先用着吧!有问题再说首先,做个 view变成原来的表结构(1张表)
create view v_test(pk_1 ,
comp,
fath_pk,
employ)
as
select td.code,td.name,upcode,te.employee from t_employee te,
(select * from t_department
start with code='1001'
connect by prior code=upcode
) td
where te.code(+)=td.code统计,多套了一层
select cpk,ccomp ,sum(num ) from (
select case when comp like '集团%' then '1001' else pk_1 end as cpk,
case when comp like '集团%' then '集团' else comp end as ccomp,(select count(distinct employ) from v_test
start with pk_1=c.pk_1
connect by prior pk_1=fath_pk) as num
from v_test c
where c.fath_pk='1001'
and c.pk_1 <> '1001'
)
group by cpk,ccomp
如果集团下属机构有2个人以上的人,统计就重复了
比如:
INSERT INTO t_employee (employee, code, name)
values ('a6', '1006', '赵八');
还是很感谢你
有其他问题再请教啊。这个效率确实不高,在考虑用function取得需要作为汇总条件的公司id后,生成临时表,再进行关联。
------暂时用的脚本-------------
SELECT cpk, ccomp, SUM(num )
FROM ( SELECT DISTINCT CASE WHEN
comp like 'lj%' THEN '1001'
ELSE pk_1
END AS cpk,
CASE WHEN
comp LIKE 'lj%' THEN '集团'
ELSE comp
END AS ccomp,
( SELECT COUNT( DISTINCT employ )
FROM t_test
START WITH pk_1=c.pk_1
CONNECT BY PRIOR pk_1 = fath_pk ) AS num
FROM t_test c
WHERE c.fath_pk='1001'
AND c.pk_1 != '1001' )