34,575
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE gsm_config_cert_type
(fd_id int,fd_name varchar(3))
insert into gsm_config_cert_type
select 1,'tx1' union all
select 2,'tx2' union all
select 3,'tx3'
CREATE TABLE gsm_config_cert_org
(fd_id int,fd_name varchar(3))
insert into gsm_config_cert_org
select 1,'jg1' union all
select 2,'jg2' union all
select 3,'jg3'
CREATE TABLE gsm_pm_staff
(fd_id int,fd_name varchar(4))
insert into gsm_pm_staff
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五' union all
select 4,'马六'
CREATE TABLE gsm_config_cert_range
(fd_id int,fd_code varchar(8),fd_order int,fd_org_id int,fd_type_id int,fd_parent_id int)
insert into gsm_config_cert_range
select 1,'01',1,1,1,null union all
select 2,'01',2,1,2,null union all
select 3,'01',3,2,1,null union all
select 4,'01',4,2,2,null union all
select 5,'01.01.01',103,1,1,1 union all
select 6,'01.18.02',104,1,1,1 union all
select 7,'17.11.23',105,1,1,1 union all
select 8,'01.01.01',106,1,2,2 union all
select 9,'01.17.14',107,1,2,2 union all
select 10,'01.02.03',108,1,2,2 union all
select 11,'19.00.03',109,2,1,3 union all
select 12,'19.01.01',110,2,1,3 union all
select 13,'14.01.03',111,2,1,3 union all
select 14,'14.02.04',112,2,1,3
CREATE TABLE gsm_pm_assessor
(fd_id int,fd_code varchar(28),fd_person_id int,fd_type_id varchar(8),fd_org_id int)
insert into gsm_pm_assessor
select 1,'01.01.01;01.18.02',1,1,1 union all
select 2,'01.01.01;17.11.23',2,1,1 union all
select 3,'01.01.01;01.18.02;17.11.23',3,1,1 union all
select 4,'01.01.01',1,1,2 union all
select 5,'01.17.14;01.01.01',3,1,2 union all
select 6,'01.02.03;01.17.14',2,1,2 union all
select 7,'19.00.03;14.01.03;14.02.04',4,2,1 union all
select 8,'14.01.03;14.02.04',3,2,1 union all
select 9,'14.01.03;14.02.04',2,2,1 union all
select 10,'19.01.01',2,2,1;
--创建所有表
DECLARE @n INT
SET @n = 50 ;
WITH numcte
AS ( SELECT 1 AS n
UNION ALL
SELECT n + 1
FROM numcte
WHERE n < @n
)
SELECT *
INTO Nums
FROM numcte
--创建一个数字辅助表Nums
;
WITH assessor
AS ( SELECT SUBSTRING(a.fd_code, n,
CHARINDEX(';', a.fd_code + ';', n) - n) AS 代码 ,
t.fd_name 体系 ,
o.fd_name 机构 ,
s.fd_name 人员
FROM dbo.gsm_pm_assessor a
JOIN Nums ON n <= LEN(fd_code)
AND SUBSTRING(';' + fd_code, n, 1) = ';'
LEFT JOIN dbo.gsm_config_cert_type t ON a.fd_type_id = t.fd_id
LEFT JOIN dbo.gsm_config_cert_org o ON a.fd_org_id = o.fd_id
LEFT JOIN dbo.gsm_pm_staff s ON a.fd_person_id = s.fd_id
WHERE EXISTS ( SELECT 1
FROM dbo.gsm_config_cert_range r
WHERE CHARINDEX(r.fd_code, a.fd_code) > 0 )
)
SELECT *
INTO #assessor--创建临时表
FROM assessor
SELECT a1.代码 ,
a1.体系 ,
a1.机构 ,
STUFF(( SELECT ',' + a2.人员
FROM #assessor a2
WHERE a1.代码 = a2.代码
AND a1.体系 = a2.体系
AND a1.机构 = a2.机构
FOR
XML PATH('')
), 1, 1, '') AS 人员 ,
( SELECT COUNT(1)
FROM #assessor a3
WHERE a1.代码 = a3.代码
AND a1.体系 = a3.体系
AND a1.机构 = a3.机构
) AS 人数
INTO tbxls--将最终结果插入tbxls,此行去掉直接得出查询结果
FROM #assessor a1
GROUP BY a1.代码 ,
a1.体系 ,
a1.机构
ORDER BY a1.体系 ,
a1.机构;
EXEC master..xp_cmdshell 'bcp ^^.dbo.tbxls out c:\1.xls -c -q -S@@ -U## -P&&'--tbxls导出excel,位置:c:\1.xls
--@@替换成服务器名,##替换成登陆用户名,&&替换成登陆密码,^^替换成当前数据库名
--@@替换成服务器名,##替换成登陆用户名,&&替换成登陆密码,^^替换成当前数据库名
DROP TABLE #assessor,dbo.gsm_config_cert_org,dbo.gsm_config_cert_range,dbo.gsm_config_cert_type,dbo.gsm_pm_assessor,dbo.gsm_pm_staff,dbo.Nums,dbo.tbxls
代码 体系 机构 人员 人数
01.01.01 tx1 jg1 张三;李四;王五 3
01.01.01 tx1 jg2 张三;王五 2
#1.为什么是2个,把分组的依据给大家说一下。
#2.基本上应该是把gsm_pm_assessor先拆分成多行,再join其它表,再group by并且合并人员字段
--提供测试数据
declare @gsm_config_cert_type table
(fd_id int,fd_name varchar(3))
insert into @gsm_config_cert_type
select 1,'tx1' union all
select 2,'tx2' union all
select 3,'tx3'
declare @gsm_config_cert_org table
(fd_id int,fd_name varchar(3))
insert into @gsm_config_cert_org
select 1,'jg1' union all
select 2,'jg2' union all
select 3,'jg3'
declare @gsm_pm_staff table
(fd_id int,fd_name varchar(4))
insert into @gsm_pm_staff
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五' union all
select 4,'马六'
declare @gsm_config_cert_range table
(fd_id int,fd_code varchar(8),fd_order int,fd_org_id int,fd_type_id int,fd_parent_id int)
insert into @gsm_config_cert_range
select 1,'01',1,1,1,null union all
select 2,'01',2,1,2,null union all
select 3,'01',3,2,1,null union all
select 4,'01',4,2,2,null union all
select 5,'01.01.01',103,1,1,1 union all
select 6,'01.18.02',104,1,1,1 union all
select 7,'17.11.23',105,1,1,1 union all
select 8,'01.01.01',106,1,2,2 union all
select 9,'01.17.14',107,1,2,2 union all
select 10,'01.02.03',108,1,2,2 union all
select 11,'19.00.03',109,2,1,3 union all
select 12,'19.01.01',110,2,1,3 union all
select 13,'14.01.03',111,2,1,3 union all
select 14,'14.02.04',112,2,1,3
declare @gsm_pm_assessor table
(fd_id int,fd_code varchar(28),fd_person_id int,fd_type_id varchar(8),fd_org_id int)
insert into @gsm_pm_assessor
select 1,'01.01.01;01.18.02',1,1,1 union all
select 2,'01.01.01;17.11.23',2,1,1 union all
select 3,'01.01.01;01.18.02;17.11.23',3,1,1 union all
select 4,'01.01.01',1,1,2 union all
select 5,'01.17.14;01.01.01',3,1,2 union all
select 6,'01.02.03;01.17.14',2,1,2 union all
select 7,'19.00.03;14.01.03;14.02.04',4,2,1 union all
select 8,'14.01.03;14.02.04',3,2,1 union all
select 9,'14.01.03;14.02.04',2,2,1 union all
select 10,'19.01.01',2,2,1
;with t as (
select distinct a.fd_code,
(select fd_name from @gsm_config_cert_type where a.fd_type_id=fd_id) as type,
(select fd_name from @gsm_config_cert_org where a.fd_org_id=fd_id) as org,
(select fd_name from @gsm_pm_staff where b.fd_person_id=fd_id) as person
from @gsm_config_cert_range A, @gsm_pm_assessor B
where charindex(a.fd_code,b.fd_code)>0
and a.fd_org_id=b.fd_org_id
and a.fd_type_id=b.fd_type_id
and a.fd_parent_id is not null),
t1 as (
select fd_code,type,org,
[value]=( SELECT [person] +','
FROM t AS b
WHERE b.fd_code = a.fd_code
and a.org=b.org
and a.type=b.type
order by person desc
FOR XML PATH(''))
FROM t AS a )
select fd_code,type,org,substring(value,1,len(value)-1) as name,count(*) as CNT from t1
group by fd_code,type,org,value