IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb (id INT,col VARCHAR(30)) INSERT INTO tb VALUES (1,'aa,bb') INSERT INTO tb VALUES (2,'aaa,bbb,ccc') go --1.2000/2005通用方法 SELECT a.id, col=SUBSTRING(a.col,number,CHARINDEX(',',a.col+',',number)-b.number) FROM tb a JOIN master..spt_values b ON b.type='P' --AND SUBSTRING(','+a.col,b.number,1)=',' --用此条件或下面的条件均可 AND CHARINDEX(',',','+a.col,number)=number
--2.2005以上新方法: SELECT a.id,b.col FROM (SELECT id,col=CAST('<v>'+REPLACE(col,',','</v><v>')+'</v>' AS xml) FROM tb) a OUTER APPLY (SELECT col=T.C.value('.','varchar(50)') FROM a.col.nodes('/v') AS T(C)) b--结果: /* id col ----------- -------------------------------------------------- 1 aa 1 bb 2 aaa 2 bbb 2 ccc(5 行受影响) */
2005及以上版本可用,LZ参考参考。 CREATE TABLE #User ( id INT IDENTITY, account VARCHAR(20) ) CREATE TABLE #UserAccount ( account INT , NAME VARCHAR(20) )INSERT INTO #User SELECT '1,3,5' INSERT INTO #User SELECT '2,3' INSERT INTO #User SELECT '3,4,5'INSERT INTO #UserAccount SELECT 1,'Evan' INSERT INTO #UserAccount SELECT 2,'Shadow' INSERT INTO #UserAccount SELECT 3,'Nick' INSERT INTO #UserAccount SELECT 4,'Jack' INSERT INTO #UserAccount SELECT 5,'Tim'SELECT * FROM #User uSELECT * FROM #UserAccount u;WITH CTE AS ( SELECT a.id,U.[NAME] FROM (SELECT id,col=CAST('<v>'+REPLACE(account,',','</v><v>')+'</v>' AS xml) FROM #User) a OUTER APPLY (SELECT col=T.C.value('.','varchar(50)') FROM a.col.nodes('/v') AS T(C)) b LEFT JOIN #UserAccount U ON U.account=B.COL ) SELECT ID, [NAME]=STUFF((SELECT ',' +[NAME] FROM CTE C WHERE C.ID=T.ID FOR XML PATH('')),1,1,'') FROM CTE T GROUP BY IDID NAME ----------- ----------------------------------- 1 Evan,Nick,Tim 2 Shadow,Nick 3 Nick,Jack,Tim(3 row(s) affected)
with cte as ( select t1.员工工号,t2.员工名字 from 表 t1 left join 员工表 t2 on charindex(','+t2.员工工号+',',','+t1.员工工号+',')>0 ) --这就查询结果为 /* 员工工号 员工名字 1,2,3 1的名字 1,2,3 2的名字 1,2,3 3的名字 */--再把相同的员工工号拼接起来select 员工工号, stuff((select ','+员工名字 from cte where 员工工号=t.员工工号 for xml path('')),1,1,'') from cte t group by 员工工号--结果为 /* 员工工号 员工名字 1,2,3 1的名字,2的名字,3的名字 */
CREATE TABLE 表 ( id INT IDENTITY, 员工工号VARCHAR(20) ) CREATE TABLE 员工表 ( 员工工号INT , 员工名字VARCHAR(20) )INSERT INTO 表SELECT '1,3,5' INSERT INTO 表SELECT '2,3' INSERT INTO 表SELECT '3,4,5'INSERT INTO 员工表SELECT 1,'Evan' INSERT INTO 员工表SELECT 2,'Shadow' INSERT INTO 员工表SELECT 3,'Nick' INSERT INTO 员工表SELECT 4,'Jack' INSERT INTO 员工表SELECT 5,'Tim'with cte as ( select t1.员工工号,t2.员工名字 from 表t1 left join 员工表t2 on charindex(','+cast(t2.员工工号as varchar)+',',','+t1.员工工号+',')>0 ) select 员工工号, stuff((select ','+员工名字from cte where 员工工号=t.员工工号for xml path('')),1,1,'') 员工名字 from cte t group by 员工工号/*员工工号 员工名字 1,3,5 Evan,Nick,Tim 2,3 Shadow,Nick 3,4,5 Nick,Jack,Tim*/
DROP TABLE tb
GO
CREATE TABLE tb (id INT,col VARCHAR(30))
INSERT INTO tb VALUES (1,'aa,bb')
INSERT INTO tb VALUES (2,'aaa,bbb,ccc')
go
--1.2000/2005通用方法
SELECT
a.id,
col=SUBSTRING(a.col,number,CHARINDEX(',',a.col+',',number)-b.number)
FROM tb a
JOIN master..spt_values b
ON b.type='P'
--AND SUBSTRING(','+a.col,b.number,1)=',' --用此条件或下面的条件均可
AND CHARINDEX(',',','+a.col,number)=number
--2.2005以上新方法:
SELECT a.id,b.col
FROM (SELECT id,col=CAST('<v>'+REPLACE(col,',','</v><v>')+'</v>' AS xml) FROM tb) a
OUTER APPLY (SELECT col=T.C.value('.','varchar(50)') FROM a.col.nodes('/v') AS T(C)) b--结果:
/*
id col
----------- --------------------------------------------------
1 aa
1 bb
2 aaa
2 bbb
2 ccc(5 行受影响)
*/
CREATE TABLE #User
(
id INT IDENTITY,
account VARCHAR(20)
)
CREATE TABLE #UserAccount
(
account INT ,
NAME VARCHAR(20)
)INSERT INTO #User SELECT '1,3,5'
INSERT INTO #User SELECT '2,3'
INSERT INTO #User SELECT '3,4,5'INSERT INTO #UserAccount SELECT 1,'Evan'
INSERT INTO #UserAccount SELECT 2,'Shadow'
INSERT INTO #UserAccount SELECT 3,'Nick'
INSERT INTO #UserAccount SELECT 4,'Jack'
INSERT INTO #UserAccount SELECT 5,'Tim'SELECT * FROM #User uSELECT * FROM #UserAccount u;WITH CTE
AS
(
SELECT a.id,U.[NAME]
FROM (SELECT id,col=CAST('<v>'+REPLACE(account,',','</v><v>')+'</v>' AS xml) FROM #User) a
OUTER APPLY (SELECT col=T.C.value('.','varchar(50)') FROM a.col.nodes('/v') AS T(C)) b
LEFT JOIN #UserAccount U
ON U.account=B.COL
)
SELECT ID, [NAME]=STUFF((SELECT ',' +[NAME] FROM CTE C WHERE C.ID=T.ID FOR XML PATH('')),1,1,'')
FROM CTE T
GROUP BY IDID NAME
----------- -----------------------------------
1 Evan,Nick,Tim
2 Shadow,Nick
3 Nick,Jack,Tim(3 row(s) affected)
select t1.员工工号,t2.员工名字
from 表 t1
left join 员工表 t2
on charindex(','+t2.员工工号+',',','+t1.员工工号+',')>0 )
--这就查询结果为
/*
员工工号 员工名字
1,2,3 1的名字
1,2,3 2的名字
1,2,3 3的名字
*/--再把相同的员工工号拼接起来select 员工工号,
stuff((select ','+员工名字 from cte where 员工工号=t.员工工号 for xml path('')),1,1,'')
from cte t
group by 员工工号--结果为
/*
员工工号 员工名字
1,2,3 1的名字,2的名字,3的名字
*/
CREATE TABLE 表
(
id INT IDENTITY,
员工工号VARCHAR(20)
)
CREATE TABLE 员工表
(
员工工号INT ,
员工名字VARCHAR(20)
)INSERT INTO 表SELECT '1,3,5'
INSERT INTO 表SELECT '2,3'
INSERT INTO 表SELECT '3,4,5'INSERT INTO 员工表SELECT 1,'Evan'
INSERT INTO 员工表SELECT 2,'Shadow'
INSERT INTO 员工表SELECT 3,'Nick'
INSERT INTO 员工表SELECT 4,'Jack'
INSERT INTO 员工表SELECT 5,'Tim'with cte as (
select t1.员工工号,t2.员工名字
from 表t1
left join 员工表t2
on charindex(','+cast(t2.员工工号as varchar)+',',','+t1.员工工号+',')>0 )
select 员工工号,
stuff((select ','+员工名字from cte where 员工工号=t.员工工号for xml path('')),1,1,'') 员工名字
from cte t
group by 员工工号/*员工工号 员工名字
1,3,5 Evan,Nick,Tim
2,3 Shadow,Nick
3,4,5 Nick,Jack,Tim*/