现有 字典表tba
字段如下
dm nr
01 新增
02 修改
03 统计
04 XX
05 aaa
06 ...用户表 user (字段qx对应 字典表tba的dm,每个id可以有多个功能,用逗号分隔 )id qxadmin 功能1,功能2,功能3
001 01
002 01,02
003 01,03
004 05现在想显示这样的效果 (把qx地段里的代码替换成 字段表tba里的nr)admin 新增,修改,统计
001 新增
002 新增,修改
003 新增,统计
004 aaa这个sql语句该怎样写呀? 谢谢呀分隔字典替换
--导入测试数据
declare @tba table (dm varchar(10), nr varchar(10))
insert into @tba select '01','新增' union all
select '02','修改' union all
select '03','统计' union all
select '04','XX' union all
select '05','aaa'
declare @user table (id varchar(10), qx varchar(100))
insert into @user select '001','01' union all
select '002','01,02' union all
select '003','01,03' union all
select '004','05'--主要思路是先拆分,后合并--建立表格
if object_id('tab') is not null drop table tab
create table tab (id varchar(10), qxnr varchar(100))
insert into tab
select id,nr from @user a inner join @tba b on
CHARINDEX(','+b.dm+',',','+a.qx+',')>0
order by id--建立自定义函数(用于合并数据)
if object_id('F_Str') is not null drop function F_Str
go
CREATE function F_Str(@id int)
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+qxnr from Tab where id=@id
return @S
end
go--输出结果
select distinct id,dbo.F_Str(id) as nr from tab
/*
id nr
---------- ----------------------------------------------------------------------------------------------------
001 新增
002 新增,修改
003 新增,统计
004 aaa(所影响的行数为 4 行)
*/
declare @tba table (dm varchar(10), nr varchar(10))
insert into @tba select '01','新增' union all
select '02','修改' union all
select '03','统计' union all
select '04','XX' union all
select '05','aaa'
declare @tbb table (id varchar(10), qx varchar(100))
insert into @tbb select '001','01' union all
select '002','01,02' union all
select '003','01,03' union all
select '004','05'
select id,qx=stuff((select ','+nr from @tba a where charindex(','+a.dm+',',','+b.qx+',')>0 for xml path('')),1,1,'') from @tbb b
(col1 int, col2 int, col3 varchar(3))
insert into ym
select 1, 1, 'A' union all
select 1, 1, 'B' union all
select 1, 2, 'C' union all
select 1, 3, 'D' union all
select 1, 3, 'E'
select a.col1,a.col2,
stuff((select ','+col3 from ym b
where b.col1=a.col1 and b.col2=a.col2
for xml path('')),1,1,'') 'col3'
from ym a
group by a.col1,a.col2