34,576
社区成员
发帖
与我相关
我的任务
分享
select *,
STUFF((
select ','+RoleName
from
(select a.UserID,b.RoleID,b.RoleName from tbluser a
left join tblrole b on Charindex(';'+cast(b.roleid as varchar(12))+';',';'+a.roleid+';')>0
) c where c.UserID=d.UserID FOR XML PATH('')), 1, 1, '') as RoleNameList,
STUFF((
select ','+problemname
from
(select a.UserID,b.problemid,b.problemname from tbluser a
left join tblproblem b on Charindex(';'+cast(b.problemid as varchar(12))+';',';'+a.problemid+';')>0
) c where c.UserID=d.UserID FOR XML PATH('')), 1, 1, '') as problemNameList
from tbluser d
/* 结果
1 name1 1;2;3 1;2 role1,role2,role3 problem1,problem2
2 name2 2 2;3 role2 problem2,problem3
3 name3 2;3 1;2 role2,role3 problem1,problem2
create table t1(id int,name varchar(10),classids varchar(20))
insert into t1 select 1,'张三','1,2,3'
insert into t1 select 2,'李四','2,3'
insert into t1 select 3,'王五','1,3'
create table t2(classid int,classname varchar(20))
insert into t2 select 1,'数学'
insert into t2 select 2,'语文'
insert into t2 select 3,'英语'
go
create function f_str(@str varchar(400))
returns varchar(100)
as
begin
set @str=','+@str+','
select @str=replace(@str,','+rtrim(classid)+',',','+classname+',') from t2
set @str=substring(@str,2,len(@str)-2)
return @str
end
go
select *,dbo.f_str(classids) as classnames from t1
/*
id name classids classnames
----------- ---------- -------------------- ----------------------------------------------------------------------------------------------------
1 张三 1,2,3 数学,语文,英语
2 李四 2,3 语文,英语
3 王五 1,3 数学,英语
*/
go
drop function f_str
drop table t1,t2
go
--> Test Data: [tbluser]
if object_id('[tbluser]') is not null drop table [tbluser]
create table [tbluser] ([userid] int,[username] varchar(5),[roleid] varchar(50),[problemid] varchar(50))
insert into [tbluser]
select 1,'name1','1;2;3','1;2' union all
select 2,'name2','2','2;3' union all
select 3,'name3','2;3','1;2'
--> Test Data: [tblrole]
if object_id('[tblrole]') is not null drop table [tblrole]
create table [tblrole] ([roleid] int,[rolename] varchar(5))
insert into [tblrole]
select 1,'role1' union all
select 2,'role2' union all
select 3,'role3'
--> Test Data: [tblproblem]
if object_id('[tblproblem]') is not null drop table [tblproblem]
create table [tblproblem] ([problemid] int,[problemname] varchar(8))
insert into [tblproblem]
select 1,'problem1' union all
select 2,'problem2' union all
select 3,'problem3'
--select * from [tbluser]
--select * from [tblrole]
--select * from [tblproblem]
--Code
go
create function f_hb(@id varchar(10))
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str=''
select @str=@str+';'+[problemname] from tblproblem where charindex(';'+cast(problemid as varchar)+';',';'+@id+';')>0
return stuff(@str,1,1,'')
end
go
create function f_hb2(@id varchar(10))
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str=''
select @str=@str+';'+[rolename] from tblrole where charindex(';'+cast(roleid as varchar)+';',';'+@id+';')>0
return stuff(@str,1,1,'')
end
go
select userid,username,rolename=dbo.f_hb2(roleid),problemname=dbo.f_hb(problemid) from tbluser
drop function f_hb,f_hb2
--Drop
--Result
/*
userid username rolename problemname
----------- -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 name1 role1;role2;role3 problem1;problem2
2 name2 role2 problem2;problem3
3 name3 role2;role3 problem1;problem2
*/
---------------------------------
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @tbluser
DECLARE @tbluser TABLE (userid INT,username VARCHAR(5),roleid VARCHAR(5),problemid VARCHAR(3))
INSERT INTO @tbluser
SELECT 1,'name1','1;2;3','1;2' UNION ALL
SELECT 2,'name2','2','2;3' UNION ALL
SELECT 3,'name3','2;3','1;2'
--> 生成测试数据: @tblrole
DECLARE @tblrole TABLE (roleid INT,rolename VARCHAR(5))
INSERT INTO @tblrole
SELECT 1,'role1' UNION ALL
SELECT 2,'role2' UNION ALL
SELECT 3,'role3'
--> 生成测试数据: @tblproblem
DECLARE @tblproblem TABLE (problemid INT,problemname VARCHAR(8))
INSERT INTO @tblproblem
SELECT 1,'problem1' UNION ALL
SELECT 2,'problem2' UNION ALL
SELECT 3,'problem3'
--SQL查询如下:
SELECT
A.userid,
A.username,
STUFF(B.xmldoc.value('.','varchar(50)'),1,1,'') AS rolename,
STUFF(C.xmldoc.value('.','varchar(50)'),1,1,'') AS problemname
FROM @tbluser AS A
CROSS APPLY (
SELECT xmldoc=
(
SELECT
',' + rolename AS [text()]
FROM @tblrole
WHERE ';'+A.roleid +';' LIKE '%;' + RTRIM(roleid) + ';%'
FOR XML PATH(''),TYPE
)
) AS B
CROSS APPLY (
SELECT xmldoc=
(
SELECT
',' + problemname AS [text()]
FROM @tblproblem
WHERE ';'+A.problemid +';' LIKE '%;' + RTRIM(problemid) + ';%'
FOR XML PATH(''),TYPE
)
) AS C
/*
userid username rolename problemname
----------- -------- -------------------------------------------------- --------------------------------------------------
1 name1 role1,role2,role3 problem1,problem2
2 name2 role2 problem2,problem3
3 name3 role2,role3 problem1,problem2
(3 row(s) affected)
*/
分解字符串包含的信息值后然后合并到另外一表的信息
(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-12-23 广东深圳)
/*问题描述
tba
ID classid name
1 1,2,3 西服
2 2,3 中山装
3 1,3 名裤
tbb
id classname
1 衣服
2 上衣
3 裤子
我得的结果是
id classname name
1 衣服,上衣,裤子 西服
2 上衣,裤子 中山装
3 衣服,裤子 名裤
*/
-----------------------------------------------------
--sql server 2000中的写法
create table tba(ID int,classid varchar(20),name varchar(10))
insert into tba values(1,'1,2,3','西服')
insert into tba values(2,'2,3' ,'中山装')
insert into tba values(3,'1,3' ,'名裤')
create table tbb(ID varchar(10), classname varchar(10))
insert into tbb values('1','衣服')
insert into tbb values('2','上衣')
insert into tbb values('3','裤子')
go
--第1种方法,创建函数来显示
create function f_hb(@id varchar(10))
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str=''
select @str=@str+','+[classname] from tbb where charindex(','+cast(id as varchar)+',',','+@id+',')>0
return stuff(@str,1,1,'')
end
go
select id,classid=dbo.f_hb(classid),name from tba
drop function f_hb
/*
id classid name
----------- ------------- ----------
1 衣服,上衣,裤子 西服
2 上衣,裤子 中山装
3 衣服,裤子 名裤
(所影响的行数为 3 行)
*/
--第2种方法.update
while(exists (select * from tba,tbb where charindex(tbb.id,tba.classid) >0))
update tba
set classid= replace(classid,tbb.id,tbb.classname)
from tbb
where charindex(tbb.id,tba.classid)>0
select * from tba
/*
ID classid name
----------- -------------------- ----------
1 衣服,上衣,裤子 西服
2 上衣,裤子 中山装
3 衣服,裤子 名裤
(所影响的行数为 3 行)
*/
drop table tba,tbb
------------------------------------------------------------------------
--sql server 2005中先分解tba中的classid,然后再合并classname
create table tba(ID int,classid varchar(20),name varchar(10))
insert into tba values(1,'1,2,3','西服')
insert into tba values(2,'2,3' ,'中山装')
insert into tba values(3,'1,3' ,'名裤')
create table tbb(ID varchar(10), classname varchar(10))
insert into tbb values('1','衣服')
insert into tbb values('2','上衣')
insert into tbb values('3','裤子')
go
SELECT id , classname , name FROM
(
SELECT DISTINCT id , name FROM (select tbc.id , tbc.name , tbb.classname from
(
SELECT A.id , A.name , B.classid FROM(SELECT id , name , [classid] = CONVERT(xml,'<root><v>' + REPLACE([classid], ',', '</v><v>') + '</v></root>') FROM tba)A
OUTER APPLY(SELECT classid = N.v.value('.', 'varchar(100)') FROM A.[classid].nodes('/root/v') N(v))B
) tbc , tbb where tbc.classid = tbb.id
) T
)A
OUTER APPLY
(
SELECT [classname]= STUFF(REPLACE(REPLACE((
SELECT classname FROM (select tbc.id , tbc.name , tbb.classname from
(
SELECT A.id , A.name , B.classid FROM(SELECT id , name , [classid] = CONVERT(xml,'<root><v>' + REPLACE([classid], ',', '</v><v>') + '</v></root>') FROM tba)A
OUTER APPLY(SELECT classid = N.v.value('.', 'varchar(100)') FROM A.[classid].nodes('/root/v') N(v))B
) tbc , tbb where tbc.classid = tbb.id
) N
WHERE id = A.id and name = A.name
FOR XML AUTO), '<N classname="', ','), '"/>', ''), 1, 1, '')
)N
order by id
drop table tba,tbb
/*
id classname name
----------- -------------- ----------
1 衣服,上衣,裤子 西服
2 上衣,裤子 中山装
3 衣服,裤子 名裤
(3 行受影响)
*/