建議A表格構更改﹐不要將權限放在一個字段﹐而折成一條條記錄﹐也就是:
Tag Security
5 2988
5 3059
5 3086
......如果不可以﹐則在和B表關聯時﹐先將其改成以上表格式﹐用臨時表﹐以下function供參考
CREATE FUNCTION dbo.split(@expression VarChar(5000),@delimiter Char(1) = ' ')
RETURNS @TmpTable TABLE (RefNo VarChar(300))
AS
BEGIN
BEGIN
WHILE LEN(RTRIM(@expression)) > 0
BEGIN
IF CHARINDEX(@delimiter,@expression,0)=0
Begin
INSERT INTO @TmpTable (refno) VALUES(@expression)
Break
End
ELSE
Begin
INSERT INTO @TmpTable(refno) Values( Left(@expression,CharIndex(@delimiter,@expression) - 1))
Set @expression = SubString(@expression,CharIndex(@delimiter,@expression) + 1,Len(@expression))
End
END
END
RETURN
ENDgo
select * into #temp from dbo.split('2988,3059,3086,2463,2302,1611,2366,2231,.....', ',')
select * from #temp inner join b表 on refno = b表.bl_ID
Tag Security
5 2988
5 3059
5 3086
......如果不可以﹐則在和B表關聯時﹐先將其改成以上表格式﹐用臨時表﹐以下function供參考
CREATE FUNCTION dbo.split(@expression VarChar(5000),@delimiter Char(1) = ' ')
RETURNS @TmpTable TABLE (RefNo VarChar(300))
AS
BEGIN
BEGIN
WHILE LEN(RTRIM(@expression)) > 0
BEGIN
IF CHARINDEX(@delimiter,@expression,0)=0
Begin
INSERT INTO @TmpTable (refno) VALUES(@expression)
Break
End
ELSE
Begin
INSERT INTO @TmpTable(refno) Values( Left(@expression,CharIndex(@delimiter,@expression) - 1))
Set @expression = SubString(@expression,CharIndex(@delimiter,@expression) + 1,Len(@expression))
End
END
END
RETURN
ENDgo
select * into #temp from dbo.split('2988,3059,3086,2463,2302,1611,2366,2231,.....', ',')
select * from #temp inner join b表 on refno = b表.bl_ID
drop function [dbo].[f_splitSTR]
GO/*--字符串分函数 分拆指定分隔符的的字符串,返回结果表--邹建 2004.04(引用请保留此信息)--*//*--调用示例 SELECT * FROM f_splitSTR('aa ba b','a ')
--*/
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --要分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO
declare @id varchar(8000)
select @id=id from a where 条件
Select Top 20 b.* From B, dbo.f_splitstr(@id,',') a Where b.Tag=5 b.bl_ID =a.id