有点烦人的sql语句

[weasea]

员工有多样权限，有多个负责解决的问题
tbluser
userid username roleid problemid
1 name1 1;2;3 1;2
2 name2 2 2;3
3 name3 2;3 1;2

tblrole
roleid rolename
1 role1
2 role2
3 role3

tblproblem
problemid problemname
1 problem1
2 problem2
3 problem3

需要查出 userid username rolename problename

谢谢

[liangCK]

2000?2005?

[快乐_石头]

分解字符串包含的信息值后然后合并到另外一表的信息
(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  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 行受影响)
*/

[weasea]

2000
是否2000和2005有句子区别啊

[jinjazz]

建议你改表结构，或者增加一张对照表

[weasea]

请楼上给出详细建议
谢谢

[--小F--]

仰视各位大侠

[liangCK]

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

[liangCK]

我写的是2005的.2000要写函数.

[wxg22526451]

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

[mugua604]

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

[weasea]

再次仰望。。。。。

[rmini]

三个表的联合查询

[tengjian1981]

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

[tengjian1981]

sqlserver 2005语法

[shenzhenNBA]

我的也出来了，呵呵，慢点了 ，三表联动，都结萜了，还是发吧

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=0

while @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=1

while @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

[shenzhenNBA]

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

少了一个地方，修改

[shenzhenNBA]

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

少了一个地方，修改