假設有一張teacher的權限表,也就是teacher與查看學生的類型的關係表 如:tbl。 更詳細的例子select 姓名 from student where ','+(stuff(select ','+类型 from tbl where teachername='张老师' for xml path(''),1,1,''))+',' like '%,'+类型+',%'
試一下下面的sql,例子中的數據為‘1,2,3’,可以改變參數@groupNo的數據。create table #student ( ID int identity(1,1), name nvarchar(10), GroupNo varchar(50) )insert #student select '張三','1' union all select '李四','2' union all select '王五','3' union all select '小六','1,2' union all select '小米','1,3' union all select '小皇','2,3' union all select '大禹','1,2,3'declare @groupNo varchar(20)='1,2,3' declare @sql varchar(500) declare @i int declare @id varchar(10)='' declare @sqlFilter varchar(100)='' set @i=PATINDEX('%[,]%',@groupNo) while(@i>0) begin set @id=SUBSTRING(@groupNo,1,@i-1) set @groupNo=SUBSTRING(@groupNo,@i+1,LEN(@groupNo)) set @i=PATINDEX('%[,]%',@groupNo) set @sqlFilter=@sqlFilter+' or charindex('''+@id+''',a.GroupNo)>0' end print @groupNO if(LEN(@groupNO)>0) set @sqlFilter=@sqlFilter+' or charindex('''+@groupNO+''',a.GroupNo)>0' if(len(@sqlFilter)>0) begin set @sql='select * from #student as a where '+SUBSTRING(@sqlFilter,4,LEN(@sqlFilter)) end else begin set @sql='select * from #student as a' end set @sql=@sql+' drop table #student' print @sql exec(@sql)
可以將上面的else語句去掉,上面只為做測試。else begin set @sql='select * from #student as a' end
SELECT [姓名] FROM (SELECT [姓名],CONVERT(XML ,'<v>'+REPLACE([类型],',','</v><v>')+'</v>')[类型S] FROM [数据表]) T0 OUTER APPLY (SELECT N.v.value('.' , 'VARCHAR(100)')[类型] FROM T0.[类型S].nodes('/v') N(v)) T1 WHERE ','+'查询字符串'+',' LIKE '%,'+[类型]+',%' GROUP BY [姓名] 查询字符串 换成你的 1,3,6,7 类似这样 数据表 换成你的表名
更詳細的例子select 姓名 from student where ','+(stuff(select ','+类型 from tbl where teachername='张老师' for xml path(''),1,1,''))+',' like '%,'+类型+',%'
試一下下面的sql,例子中的數據為‘1,2,3’,可以改變參數@groupNo的數據。create table #student
(
ID int identity(1,1),
name nvarchar(10),
GroupNo varchar(50)
)insert #student
select '張三','1' union all
select '李四','2' union all
select '王五','3' union all
select '小六','1,2' union all
select '小米','1,3' union all
select '小皇','2,3' union all
select '大禹','1,2,3'declare @groupNo varchar(20)='1,2,3'
declare @sql varchar(500)
declare @i int
declare @id varchar(10)=''
declare @sqlFilter varchar(100)=''
set @i=PATINDEX('%[,]%',@groupNo)
while(@i>0)
begin
set @id=SUBSTRING(@groupNo,1,@i-1)
set @groupNo=SUBSTRING(@groupNo,@i+1,LEN(@groupNo))
set @i=PATINDEX('%[,]%',@groupNo)
set @sqlFilter=@sqlFilter+' or charindex('''+@id+''',a.GroupNo)>0'
end
print @groupNO
if(LEN(@groupNO)>0)
set @sqlFilter=@sqlFilter+' or charindex('''+@groupNO+''',a.GroupNo)>0'
if(len(@sqlFilter)>0)
begin
set @sql='select * from #student as a where '+SUBSTRING(@sqlFilter,4,LEN(@sqlFilter))
end
else
begin
set @sql='select * from #student as a'
end
set @sql=@sql+' drop table #student'
print @sql
exec(@sql)
begin
set @sql='select * from #student as a'
end
[姓名]
FROM
(SELECT [姓名],CONVERT(XML ,'<v>'+REPLACE([类型],',','</v><v>')+'</v>')[类型S] FROM [数据表]) T0
OUTER APPLY (SELECT N.v.value('.' , 'VARCHAR(100)')[类型] FROM T0.[类型S].nodes('/v') N(v)) T1
WHERE
','+'查询字符串'+',' LIKE '%,'+[类型]+',%'
GROUP BY [姓名]
查询字符串 换成你的 1,3,6,7 类似这样
数据表 换成你的表名