有点烦人的sql语句

Question

员工有多样权限，有多个负责解决的问题
tbluser
userid  username    roleid   problemid
1        name1       1;2;3    1;2
2        name2       2         2;3
3        name3       2;3      1;2tblrole
roleid    rolename
1          role1
2          role2
3          role3tblproblem
problemid    problemname
1             problem1
2              problem2
3             problem3需要查出  userid   username   rolename    problename谢谢

Answer 1

分解字符串包含的信息值后然后合并到另外一表的信息
(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  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','裤子')
goSELECT 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 iddrop table tba,tbb/*
id          classname      name
----------- -------------- ----------
1           衣服,上衣,裤子 西服
2           上衣,裤子      中山装
3           衣服,裤子      名裤
(3 行受影响)
*/

Answer 2

---------------------------------
--  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)
*/

Answer 3

--> 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
*/

Answer 4

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
goselect *,dbo.f_str(classids) as classnames  from t1
/*
id          name       classids             classnames                                                                                           
----------- ---------- -------------------- ---------------------------------------------------------------------------------------------------- 
1           张三         1,2,3                数学,语文,英语
2           李四         2,3                  语文,英语
3           王五         1,3                  数学,英语
*/
godrop function f_str
drop table t1,t2
go

Answer 5

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

Answer 6

我的也出来了，呵呵，慢点了 ，三表联动，都结萜了，还是发吧select userid,username,dbo.getRolename(roleid),dbo.getProblemame(problemid)
from tbluser order by userid-------------------------------------------------------------------
create function getRolename(@roleid varchar(255))
returns varchar(4000)
as
begin
declare @rName varchar(255)
declare @rids varchar(255)
declare @cid int
declare @i int
set @rName=''
set @rids=@roleid
set @i=1
set @cid=0while @i<len(@rids)
begin
select @cid=convert(int,substring(@rids,@i,1))
select @rName=@rName+[rolename]+"," from [tblrole] where [roleid]=@cid
@i=@i+2
end
set @rName=left(@rName,len(@rName)-1)
return @rName
end -------------------------------------------------------------------create function getProblemame(@problemid varchar(255))
returns varchar(4000)
as
begin
declare @pName varchar(255)
declare @pids varchar(255)
declare @cpid int
declare @j int
set @pName=''
set @pids=@problemid
set @cid=0
set @j=1while @j<len(@pids)
select @cpid=convert(int,substring(@pids,@j,1))
select @rName=@rName+[problemname]+"," from [tblproblem] where [problemid]=@cpid
end 
select @pName=left(@pName,len(@pName)-1)
return @pName
end 

Answer 7

create function getProblemame(@problemid varchar(255)) 
returns varchar(4000) 
as 
begin 
declare @pName varchar(255) 
declare @pids varchar(255) 
declare @cpid int 
declare @j int 
set @pName='' 
set @pids=@problemid 
set @cid=0 
set @j=1 while @j <len(@pids) 
select @cpid=convert(int,substring(@pids,@j,1)) 
select @rName=@rName+[problemname]+"," from [tblproblem] where [problemid]=@cpid 
select @j=@j+2
end 
select @pName=left(@pName,len(@pName)-1) 
return @pName 
end 少了一个地方，修改

Answer 8

create function getRolename(@roleid varchar(255)) 
returns varchar(4000) 
as 
begin 
declare @rName varchar(255) 
declare @rids varchar(255) 
declare @cid int 
declare @i int 
set @rName='' 
set @rids=@roleid 
set @i=1 
set @cid=0 while @i <len(@rids) 
begin 
select @cid=convert(int,substring(@rids,@i,1)) 
select @rName=@rName+[rolename]+"," from [tblrole] where [roleid]=@cid 
select @i=@i+2 
end 
set @rName=left(@rName,len(@rName)-1) 
return @rName 
end 少了一个地方，修改