SQLserver2000中有三张表
栏目表C 字段:id, name(id主键自增,name 名称 字符型)
栏目_角色表C_R 字段:c_id, r_id角色表 role 字段:id ,name三张表通过栏目_角色表关联。
现在要查询所有栏目对应的角色,筛选条件是如果栏目同时有管理员和栏目管理员两个角色那么只选栏目管理员,否则选出管理员
栏目表C 字段:id, name(id主键自增,name 名称 字符型)
栏目_角色表C_R 字段:c_id, r_id角色表 role 字段:id ,name三张表通过栏目_角色表关联。
现在要查询所有栏目对应的角色,筛选条件是如果栏目同时有管理员和栏目管理员两个角色那么只选栏目管理员,否则选出管理员
union all
select c.* from c , c_r from c , c_r where c.id = c_r.c_id至于c.*包含的内容你自己输入.
select c.* from c , role where c.id = role.id and c.id not in (select c.id from c , c_r where c.id = c_r.c_id)
union all
select c.* from c , c_r from c , c_r where c.id = c_r.c_id
SELECT dbo.roleAction.columnId, dbo.columns.name, dbo.aspnet_Roles.RoleName
FROM dbo.roleAction INNER JOIN
dbo.aspnet_Roles ON dbo.roleAction.RoleId = dbo.aspnet_Roles.RoleId INNER JOIN
dbo.columns ON dbo.roleAction.columnId = dbo.columns.id
WHERE (dbo.columns.id NOT IN
(SELECT dbo.roleAction.columnId
FROM dbo.aspnet_Roles INNER JOIN
dbo.roleAction ON dbo.aspnet_Roles.RoleId = dbo.roleAction.RoleId
WHERE (dbo.aspnet_Roles.RoleName LIKE '栏目编辑%')))
UNION ALL
SELECT dbo.roleAction.columnId, dbo.columns.name, dbo.aspnet_Roles.RoleName
FROM dbo.aspnet_Roles INNER JOIN
dbo.roleAction ON dbo.aspnet_Roles.RoleId = dbo.roleAction.RoleId INNER JOIN
dbo.columns ON dbo.roleAction.columnId = dbo.columns.id
WHERE (dbo.aspnet_Roles.RoleName LIKE '栏目编辑%')