34,588
社区成员
发帖
与我相关
我的任务
分享
;with cte as
(
select A.CONTENT_ID
A.CONTENT_NAME
B.DUTY_NAME
from A
left join C on A.CONTENT_ID = C.RIGHT_CONTENT
left join B on C.RIGHT_ID = B.DUTY_ID
)
select CONTENT_ID,
CONTENT_NAME,
DUTY_NAME = stuff((select ','+DUTY_NAME from cte where CONTENT_ID = a.CONTENT_ID for xml path('')),1,1,'')
from cte a
group by CONTENT_ID,
CONTENT_NAME
CREATE TABLE #A (CONTENT_ID INT,CONTENT_NAME VARCHAR(20))
INSERT INTO #A
SELECT 1,'单点登录' UNION ALL
SELECT 2,'未读邮件' UNION ALL
SELECT 3,'待办公文' UNION ALL
SELECT 4,'待办事宜' UNION ALL
SELECT 5,'服务台待办任务'
GO
CREATE TABLE #B (DUTY_ID VARCHAR(20),DUTY_NAME VARCHAR(20))
INSERT INTO #B
SELECT 'chief','科级' UNION ALL
SELECT 'department','部门管理员' UNION ALL
SELECT 'director','处级' UNION ALL
SELECT 'leader','行领导' UNION ALL
SELECT 'staff','员工'
GO
CREATE TABLE #C --是中间表: RIGHT_ID(引用 B表:DUTY_ID), RIGHT_CONTENT(引用 A表: CONTENT_ID),
(RIGHT_ID VARCHAR(20),RIGHT_CONTENT INT)
INSERT INTO #C
SELECT 'chief',1 UNION ALL
SELECT 'department',1 UNION ALL
SELECT 'department',2 UNION ALL
SELECT 'director',2 UNION ALL
SELECT 'leader',4 UNION ALL
SELECT 'staff',5
--
---------统计出结果如下:-------------
CONTENT_ID CONTENT_NAME DUTY_NAME
1 单点登录 科级,部门管理员
2 未读邮件 部门管理员,处级
3 待办公文
4 待办事宜 行领导
5 服务台待办任务 员工
*/
--sql2005
with cte as (
select a.CONTENT_ID,a.CONTENT_NAME,ISNULL(b.DUTY_NAME,'')DUTY_NAME
from #C c
join #B b on b.DUTY_ID=c.RIGHT_ID
right join #A a on a.CONTENT_ID=c.RIGHT_CONTENT
)
SELECT CONTENT_ID,CONTENT_NAME
,STUFF((SELECT ','+DUTY_NAME FROM CTE
WHERE CONTENT_ID=e.CONTENT_ID and CONTENT_NAME=e.CONTENT_NAME FOR XML PATH('')), 1, 1, '') DUTY_NAME
FROM CTE e
GROUP BY CONTENT_ID,CONTENT_NAME
/*
CONTENT_ID CONTENT_NAME DUTY_NAME
1 单点登录 科级,部门管理员
2 未读邮件 部门管理员,处级
3 待办公文
4 待办事宜 行领导
5 服务台待办任务 员工
*/