一复杂SQL求写法,谢谢大家了!!!!

时光瞄 2011-04-29 10:49:20
先讲表结构并给出示例数据:
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_id int 外键 指向gsm_config_cert_type
fd_parent_id int 外键 指向自身
数据:
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最好了,谢谢大家!!!
...全文
189 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
qgqch2008 2011-04-30
  • 打赏
  • 举报
回复
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
Shawn 2011-04-29
  • 打赏
  • 举报
回复
代码 体系 机构 人员 人数
01.01.01 tx1 jg1 张三;李四;王五 3
01.01.01 tx1 jg2 张三;王五 2

#1.为什么是2个,把分组的依据给大家说一下。
#2.基本上应该是把gsm_pm_assessor先拆分成多行,再join其它表,再group by并且合并人员字段
时光瞄 2011-04-29
  • 打赏
  • 举报
回复
没有高手帮忙吗?
时光瞄 2011-04-29
  • 打赏
  • 举报
回复
[Quote=引用 3 楼 maco_wang 的回复:]
就是多表连查 ,然后列值拆分

SQL code

--提供测试数据
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
sel……
[/Quote]
我也知道多表查询,SQL编码能力不行,就是要循环gsm_config_cert_range里面的fd_code,然后统计gsm_pm_assessor表有多少人拥有这个代码
叶子 2011-04-29
  • 打赏
  • 举报
回复
就是多表连查 ,然后列值拆分

--提供测试数据
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
时光瞄 2011-04-29
  • 打赏
  • 举报
回复
[Quote=引用 1 楼 ssp2009 的回复:]
色盲,都看不清了。
[/Quote]
把颜色去掉了,写完好了我不少时间额。。
快溜 2011-04-29
  • 打赏
  • 举报
回复
色盲,都看不清了。
duoxu1983 2011-04-29
  • 打赏
  • 举报
回复
引用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 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
时光瞄 2011-04-29
  • 打赏
  • 举报
回复
[Quote=引用 6 楼 wwwwgou 的回复:]
SQL code
代码 体系 机构 人员 人数
01.01.01 tx1 jg1 张三;李四;王五 3
01.01.01 tx1 jg2 张三;王五 2

#1.为什么是2个,把分组的依据给大家说一下。
#2.基本上应该是把gsm_pm_assessor先拆分成多行,再join其它表,再group by并且合并人员字段
[/Quote]
01.01.01 tx1 jg1
01.01.01 tx1 jg2
这两个01.01.01其实是不同的,因为他们是同体系,不同机构的代码。
比如:
01.01.01 tx1 jg1
01.01.01 tx1 jg2
01.01.01 tx2 jg1
01.01.01 tx2 jg2
这四个代码都是不同的,虽然文本是一样的

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧