select FunItemCode,BtnName,BtnID from B,A where charindex( ','+B.ActionBtnID+',', ','+A.ActionBtnIDs+',')>0
--> 测试数据: [A] if object_id('[A]') is not null drop table [A] create table [A] (RoleID int,FunItemCode int,ActionBtnIDs varchar(100)) insert into [A] select 1,1001,'1,2,3' --> 测试数据: [B] if object_id('[B]') is not null drop table [B] create table [B] (ActionBtnID int,FunItemCode int,BtnName varchar(5),BtnID varchar(10)) insert into [B] select 1,1001,'增加','BtnInsert' union all select 2,1001,'增加2','BtnInsert2' union all select 3,1001,'增加3','BtnInsert3'--开始查询 select b.* from [A] a join [B] b on (a.FunItemCode=b.FunItemCode) where CHARINDEX(','+ltrim(b.ActionBtnID)+',',','+a.ActionBtnIDs+',')>0--结束查询 drop table [A],[B]/* ActionBtnID FunItemCode BtnName BtnID ----------- ----------- ------- ---------- 1 1001 增加 BtnInsert 2 1001 增加2 BtnInsert2 3 1001 增加3 BtnInsert3(3 行受影响)
from a, b where charindex( ','+b.ActionBtnID+',', ','+a.ActionBtnIDs+',')>0
--> 测试数据: [A]
if object_id('[A]') is not null drop table [A]
create table [A] (RoleID int,FunItemCode int,ActionBtnIDs varchar(100))
insert into [A]
select 1,1001,'1,2,3'
--> 测试数据: [B]
if object_id('[B]') is not null drop table [B]
create table [B] (ActionBtnID int,FunItemCode int,BtnName varchar(5),BtnID varchar(10))
insert into [B]
select 1,1001,'增加','BtnInsert' union all
select 2,1001,'增加2','BtnInsert2' union all
select 3,1001,'增加3','BtnInsert3'--开始查询
select b.* from [A] a join [B] b on (a.FunItemCode=b.FunItemCode)
where CHARINDEX(','+ltrim(b.ActionBtnID)+',',','+a.ActionBtnIDs+',')>0--结束查询
drop table [A],[B]/*
ActionBtnID FunItemCode BtnName BtnID
----------- ----------- ------- ----------
1 1001 增加 BtnInsert
2 1001 增加2 BtnInsert2
3 1001 增加3 BtnInsert3(3 行受影响)
自己先消化下看看
where charindex(','+ActionBtnID+','ActionBtnIDs )>0
用charindex判断最左和最右,是否有high到?
两表连接后当然就可以这么用了
看出来了,,,就是最后的值应改成 -1