一复杂SQL求写法，谢谢大家了！！！！
先讲表结构并给出示例数据：
1、gsm_config_cert_type
字段名 字段类型
fd_id int 主键
fd_name varchar(200)
数据：
1 tx1
2 tx2
3 tx3
2、gsm_config_cert_org
字段名 字段类型
fd_id int 主键
fd_name varchar(200)
数据：
1 jg1
2 jg2
3 jg3
3、gsm_pm_staff
字段名 字段类型
fd_id int 主键
fd_name varchar(200)
数据：
1 张三
2 李四
3 王五
4 马六
4、gsm_config_cert_range
字段名 字段类型
fd_id int 主键
fd_code varchar(200)
fd_order int （排序号）
fd_org_id int 外键 指向gsm_config_cert_org
fd_type_idint 外键 指向gsm_config_cert_type
fd_parent_idint 外键 指向自身
数据：
1 01 1 1 1 NULL
2 01 2 1 2 NULL
3 01 3 2 1 NULL
4 01 4 2 2 NULL
5 01.01.01 103 1 1 1
6 01.18.02 104 1 1 1
7 17.11.23 105 1 1 1
8 01.01.01 106 1 2 2
9 01.17.14 107 1 2 2
10 01.02.03 108 1 2 2
11 19.00.03 109 2 1 3
12 19.01.01 110 2 1 3
13 14.01.03 111 2 1 3
14 14.02.04 112 2 1 3
5、gsm_pm_assessor
字段名 字段类型
fd_id int
fd_code varchar(8000)
fd_person_id int 外键 指向gsm_pm_staff
fd_type_id int外键 指向gsm_config_cert_type
fd_org_id int 外键 指向gsm_config_cert_org
数据：
1 01.01.01;01.18.02 1 1 1
2 01.01.01;17.11.23 2 1 1
3 01.01.01;01.18.02;17.11.23 3 1 1
4 01.01.01 1 1 2
5 01.17.14;01.01.01 3 1 2
6 01.02.03;01.17.14 2 1 2
7 19.00.03;14.01.03;14.02.04 4 2 1
8 14.01.03;14.02.04 3 2 1
9 14.01.03;14.02.04 2 2 1
10 19.01.01 2 2 1
我想要得到的查询数据是：
代码 体系 机构 人员 人数
01.01.01 tx1 jg1 张三;李四;王五 3
01.18.02 tx1 jg1 张三;王五 2
17.11.23 tx1 jg1 李四;王五 2
01.01.01 tx1 jg2 张三;王五 2
01.17.14 tx1 jg2 王五;李四 2
01.02.03 tx1 jg2 李四 1
19.00.03 tx2 jg1 马六 1
19.01.01 tx2 jg1 李四 1
14.01.03 tx2 jg1 马六;王五;李四 3
14.02.04 tx2 jg1 马六;王五;李四 3
希望我把这个结构写清楚了，请各位高手帮帮写一下，能到处到EXCEL最好了，谢谢大家！！！
[解决办法]
就是多表连查 ，然后列值拆分
--提供测试数据declare @gsm_config_cert_type table (fd_id int,fd_name varchar(3))insert into @gsm_config_cert_typeselect 1,'tx1' union allselect 2,'tx2' union allselect 3,'tx3'declare @gsm_config_cert_org table (fd_id int,fd_name varchar(3))insert into @gsm_config_cert_orgselect 1,'jg1' union allselect 2,'jg2' union allselect 3,'jg3'declare @gsm_pm_staff table (fd_id int,fd_name varchar(4))insert into @gsm_pm_staffselect 1,'张三' union allselect 2,'李四' union allselect 3,'王五' union allselect 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_rangeselect 1,'01',1,1,1,null union allselect 2,'01',2,1,2,null union allselect 3,'01',3,2,1,null union allselect 4,'01',4,2,2,null union allselect 5,'01.01.01',103,1,1,1 union allselect 6,'01.18.02',104,1,1,1 union allselect 7,'17.11.23',105,1,1,1 union allselect 8,'01.01.01',106,1,2,2 union allselect 9,'01.17.14',107,1,2,2 union allselect 10,'01.02.03',108,1,2,2 union allselect 11,'19.00.03',109,2,1,3 union allselect 12,'19.01.01',110,2,1,3 union allselect 13,'14.01.03',111,2,1,3 union allselect 14,'14.02.04',112,2,1,3declare @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_assessorselect 1,'01.01.01;01.18.02',1,1,1 union allselect 2,'01.01.01;17.11.23',2,1,1 union allselect 3,'01.01.01;01.18.02;17.11.23',3,1,1 union allselect 4,'01.01.01',1,1,2 union allselect 5,'01.17.14;01.01.01',3,1,2 union allselect 6,'01.02.03;01.17.14',2,1,2 union allselect 7,'19.00.03;14.01.03;14.02.04',4,2,1 union allselect 8,'14.01.03;14.02.04',3,2,1 union allselect 9,'14.01.03;14.02.04',2,2,1 union allselect 10,'19.01.01',2,2,1
[解决办法]
引用3楼的表
;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 personfrom @gsm_config_cert_range A, @gsm_pm_assessor Bwhere charindex(a.fd_code,b.fd_code)>0and a.fd_org_id=b.fd_org_idand a.fd_type_id=b.fd_type_idand 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 t1group by fd_code,type,org,value
[解决办法]
CREATE TABLE gsm_config_cert_type (fd_id int,fd_name varchar(3))insert into gsm_config_cert_typeselect 1,'tx1' union allselect 2,'tx2' union allselect 3,'tx3'CREATE TABLE gsm_config_cert_org (fd_id int,fd_name varchar(3))insert into gsm_config_cert_orgselect 1,'jg1' union allselect 2,'jg2' union allselect 3,'jg3'CREATE TABLE gsm_pm_staff (fd_id int,fd_name varchar(4))insert into gsm_pm_staffselect 1,'张三' union allselect 2,'李四' union allselect 3,'王五' union allselect 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_rangeselect 1,'01',1,1,1,null union allselect 2,'01',2,1,2,null union allselect 3,'01',3,2,1,null union allselect 4,'01',4,2,2,null union allselect 5,'01.01.01',103,1,1,1 union allselect 6,'01.18.02',104,1,1,1 union allselect 7,'17.11.23',105,1,1,1 union allselect 8,'01.01.01',106,1,2,2 union allselect 9,'01.17.14',107,1,2,2 union allselect 10,'01.02.03',108,1,2,2 union allselect 11,'19.00.03',109,2,1,3 union allselect 12,'19.01.01',110,2,1,3 union allselect 13,'14.01.03',111,2,1,3 union allselect 14,'14.02.04',112,2,1,3CREATE 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_assessorselect 1,'01.01.01;01.18.02',1,1,1 union allselect 2,'01.01.01;17.11.23',2,1,1 union allselect 3,'01.01.01;01.18.02;17.11.23',3,1,1 union allselect 4,'01.01.01',1,1,2 union allselect 5,'01.17.14;01.01.01',3,1,2 union allselect 6,'01.02.03;01.17.14',2,1,2 union allselect 7,'19.00.03;14.01.03;14.02.04',4,2,1 union allselect 8,'14.01.03;14.02.04',3,2,1 union allselect 9,'14.01.03;14.02.04',2,2,1 union allselect 10,'19.01.01',2,2,1;--创建所有表DECLARE @n INTSET @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 assessorSELECT 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 a1GROUP 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