22,209
社区成员
发帖
与我相关
我的任务
分享
ClassID RightFlags
DataDiff 011010
DataDiff 011000
DataDiff 010111
DataDiff 110000
SysParam 100001
SysParam 101011
SysParam 101110
SysUsers 0001001
SysUsers 0001011
SysUsers 1110011
;with cte1 as
(
select ClassID,b.number,max(substring(RightFlags,number,1)) as r
from tb a
join master..spt_values b
on b.type='P' and number between 1 and len(RightFlags)
group by classid,number
)
select classid,
RightFlags=(select ''+ltrim(r) from cte1 where classid=t.classid order by classid,number for xml path(''))
from cte1 t
group by classid
order by classid
/**
classid RightFlags
---------- --------------------
DataDiff 111111
SysParam 101111
SysUsers 1111011
(3 行受影响)
**/
select ClassID,max(substring(RightFlags,1,1))+
max(substring(RightFlags,2,1))+
max(substring(RightFlags,3,1))+
max(substring(RightFlags,4,1))+
max(substring(RightFlags,5,1))+
max(substring(RightFlags,6,1))
from tb group by ClassID
--用了函数!
create table tb(ClassID varchar(10),RightFlags varchar(10))
insert into tb
select 'DataDiff' ,'011010' union all
select 'DataDiff' ,'011000' union all
select 'DataDiff' ,'010111' union all
select 'DataDiff' ,'110000' union all
select 'SysParam' ,'100001' union all
select 'SysParam' ,'101011' union all
select 'SysParam' ,'101110' union all
select 'SysUsers' ,'0001001' union all
select 'SysUsers' ,'0001011' union all
select 'SysUsers' ,'1110011'
go
create function f_get(@ClassID varchar(10))
returns varchar(10)
as
begin
declare @i int
declare @j int
declare @RightFlags varchar(10)
select @i = max(len(RightFlags)) from tb where ClassID = @ClassID
set @j = 1
set @RightFlags = ''
while(@j <= @i)
begin
if exists (select 1 from tb where ClassID = @ClassID and substring(RightFlags,@j,1) = '1')
set @RightFlags = @RightFlags + '1'
else
set @RightFlags = @RightFlags + '0'
set @j = @j + 1
end
return @RightFlags
end
go
select ClassID,dbo.f_get(ClassID) as RightFlags
from tb
group by ClassID
drop function f_get
drop table tb
/*
ClassID RightFlags
---------- ----------
DataDiff 111111
SysParam 101111
SysUsers 1111011
(3 行受影响)