这样3个表的关系怎么取一段字符串？？？
本帖最后由 net_08 于 2013-10-09 15:37:59 编辑
--表结构如下
DECLARE @A TABLE(ID INT,Name VARCHAR(50))
INSERT INTO @A(ID,Name)
SELECT 1,'张三' union all
SELECT 2,'李四' union all
SELECT 3,'王五'
DECLARE @B TABLE(ID INT,Name VARCHAR(50))
INSERT INTO @B(ID,Name)
SELECT 1,'设备1' union all
SELECT 2,'设备2'
DECLARE @C TABLE(ID INT,A_ID INT,B_ID INT) --表[A]和表[B]关系表
INSERT INTO @C(ID,A_ID,B_ID)
SELECT 100,1,1 UNION ALL --张三和设备1
SELECT 101,1,2 UNION ALL --张三和设备2
SELECT 102,2,1 --李四和设备1
--想得到如下结构
ID Name result
----------- ---- -----------------
1 张三 异常[设备1],设备2
2 李四 异常[设备1]
3 王五
/**
说明：以用户(A表)为最小单位查出每个用户关联的设备情况，
如果一个设备关联了两个或两个以上用户的的话，则在设备前添加 “异常” 字符。
先谢谢各位大神。
**/
DECLARE @A TABLE(ID INT,Name VARCHAR(50))
INSERT INTO @A(ID,Name)
SELECT 1,'张三' union all
SELECT 2,'李四' union all
SELECT 3,'王五'
DECLARE @B TABLE(ID INT,Name VARCHAR(50))
INSERT INTO @B(ID,Name)
SELECT 1,'设备1' union all
SELECT 2,'设备2'
DECLARE @C TABLE(ID INT,A_ID INT,B_ID INT) --表[A]和表[B]关系表
INSERT INTO @C(ID,A_ID,B_ID)
SELECT 100,1,1 UNION ALL --张三和设备1
SELECT 101,1,2 UNION ALL --张三和设备2
SELECT 102,2,1 --李四和设备1
select a.ID,a.Name,
isnull(stuff((select ','+case when c.err=1 then '异常['+b.Name+']'
else b.Name end
from (select x.A_ID,x.B_ID,
case when exists(select 1 from @C y
where y.ID<>x.ID and y.B_ID=x.B_ID)
then 1 else 0 end 'err'
from @C x) c
inner join @B b on c.B_ID=b.ID
where c.A_ID=a.ID
for xml path('')),1,1,''),'') 'result'
from @A a
/*
ID Name result
----------- ---------- ---------------------
1 张三 异常[设备1],设备2
2 李四 异常[设备1]
3 王五
(3 row(s) affected)
*/
--表结构如下
create TABLE #A(ID INT,Name VARCHAR(50))
INSERT INTO #A(ID,Name)
SELECT 1,'张三' union all
SELECT 2,'李四' union all
SELECT 3,'王五'
create TABLE #B(ID INT,Name VARCHAR(50))
INSERT INTO #B(ID,Name)
SELECT 1,'设备1' union all
SELECT 2,'设备2'
create TABLE #C(ID INT,A_ID INT,B_ID INT) --表[A]和表[B]关系表
INSERT INTO #C(ID,A_ID,B_ID)
SELECT 100,1,1 UNION ALL --张三和设备1
SELECT 101,1,2 UNION ALL --张三和设备2
SELECT 102,2,1 --李四和设备1
with cte as
(
select a.ID,a.Name,b.Name as bname from (
select #C.ID as cid,#A.ID,#A.Name from #C
right join #A on #C.A_ID=#A.ID
)a
left join
(
select #C.ID as cid,#B.ID,#B.Name from #C
left join #B on #C.B_ID=#B.ID
)b on a.cid=b.cid
)
select distinct id,name,
(select STUFF((
select ','+bname from cte b where a.ID=b.ID for xml path('')),1,1,''))Result
from cte a
------------------------------------------------------------------------------------
id name Result
----------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 张三 设备1,设备2
2 李四 设备1
3 王五 NULL
(3 行受影响)
--表结构如下
DECLARE @A TABLE(ID INT,Name VARCHAR(50))
INSERT INTO @A(ID,Name)
SELECT 1,'张三' union all
SELECT 2,'李四' union all
SELECT 3,'王五'
DECLARE @B TABLE(ID INT,Name VARCHAR(50))
INSERT INTO @B(ID,Name)
SELECT 1,'设备1' union all
SELECT 2,'设备2'
DECLARE @C TABLE(ID INT,A_ID INT,B_ID INT) --表[A]和表[B]关系表
INSERT INTO @C(ID,A_ID,B_ID)
SELECT 100,1,1 UNION ALL --张三和设备1
SELECT 101,1,2 UNION ALL --张三和设备2
SELECT 102,2,1 --李四和设备1
select * from @A
select * from @B
select * from @C
--设备有有多个用户
;with T
AS
(
select * from
(
SELECT B_ID FROM @C GROUP BY B_ID HAVING COUNT(A_ID)>1
) C_1
left join @B B
on C_1.B_ID = B.ID
)
--select * from T
--加异样
,T2
AS
(
SELECT id,'异样['+name+']' AS NAME FROM T
)
--select * from T2
--存在异样的替换名称
,T3
AS
(
SELECT C.A_ID,C.B_ID,ISNULL(T2.NAME,B.NAME) AS NAME FROM @C C INNER JOIN @B B ON C.B_ID = B.ID
LEFT JOIN T2 ON B.ID = T2.ID
)
--select * from T3
--行变列
,T4
AS
(
SELECT T.A_ID,
stuff(( SELECT ',' + CONVERT(VARCHAR(128),NAME) FROM T3 T1
WHERE T.A_ID = T1.A_ID FOR XML PATH('')), 1, 1, '') 'NAME'
FROM T3 T
GROUP BY T.A_ID
)
SELECT A.*,T4.NAME FROM @A A LEFT JOIN T4 ON A.ID = T4.A_ID
