SELECT *
FROM opeartor AS o
JOIN role AS r
ON CHARINDEX(','+RTRIM(r.roleid)+',',','+roleid+',')>0
WHERE o.opid='9000001';
FROM opeartor AS o
JOIN role AS r
ON CHARINDEX(','+RTRIM(r.roleid)+',',','+roleid+',')>0
WHERE o.opid='9000001';
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @role
DECLARE @role TABLE (id INT,roleid INT,rolename VARCHAR(8))
INSERT INTO @role
SELECT 1,101,'添加人员' UNION ALL
SELECT 2,102,'删除人员' UNION ALL
SELECT 3,103,'修改人员' UNION ALL
SELECT 4,104,'查询人员'
--> 生成测试数据: @operator
DECLARE @operator TABLE (id INT,opid INT,roleid VARCHAR(11))
INSERT INTO @operator
SELECT 1,900001,'101,102,103'--SQL查询如下:SELECT *
FROM @operator AS o
JOIN @role AS r
ON CHARINDEX(','+RTRIM(r.roleid)+',',','+o.roleid+',')>0
WHERE o.opid=900001;/*
id opid roleid id roleid rolename
----------- ----------- ----------- ----------- ----------- --------
1 900001 101,102,103 1 101 添加人员
1 900001 101,102,103 2 102 删除人员
1 900001 101,102,103 3 103 修改人员(3 行受影响)*/
无法分析查询文本。
我上面不就是一条SQL吗?..--SQL查询如下:
以前的都是我建的测试数据..SELECT *
FROM @operator AS o
JOIN @role AS r
ON CHARINDEX(','+RTRIM(r.roleid)+',',','+o.roleid+',')>0
WHERE o.opid=900001;
直接将@operator换成你的operator表,@role换成你的role表.