user用户表
cuser_id,cuser_name
001 A
002 B
auth功能表
cauth_id, cauth_name
01_001 单据录入
01_002 单据审核
authhold用户权限表
cuser_id,cauth_id
001 01_001求查询用户权限(包括所有功能)
cuser_id,cauth_id,cauth_name,bhold
001 01_001 单据录入 1
001 01_002 单据审核 0
002 01_001 单据录入 0
002 01_002 单据审核 0
cuser_id,cuser_name
001 A
002 B
auth功能表
cauth_id, cauth_name
01_001 单据录入
01_002 单据审核
authhold用户权限表
cuser_id,cauth_id
001 01_001求查询用户权限(包括所有功能)
cuser_id,cauth_id,cauth_name,bhold
001 01_001 单据录入 1
001 01_002 单据审核 0
002 01_001 单据录入 0
002 01_002 单据审核 0
(case when exists(select 1 from authhold n where n.cuser_id = m.cuser_id and n.cauth_id = m.cauth_id) then 1 else 0 end) bhold
from
(
select user.cuser_id, auth.cauth_id, auth.cauth_name from user , auth
) m
order by m.cuser_id,m.cauth_id
(case when exists(select 1 from authhold n where n.cuser_id = m.cuser_id and n.cauth_id = m.cauth_id) then 1 else 0 end) bhold
from
(
select [user].cuser_id, auth.cauth_id, auth.cauth_name from [user] , auth
) m
order by m.cuser_id,m.cauth_id
* ,
(case when exists(select 1 from authhold n where n.cuser_id = m.cuser_id and n.cauth_id = m.cauth_id) then 1 else 0 end) as bhold
from
(select [user].cuser_id, auth.cauth_id, auth.cauth_name from [user] , auth) t
order by
t.cuser_id,t.cauth_id
if object_id('[user]') is not null drop table [user]
go
create table [user]([cuser_id] varchar(3),[cuser_name] varchar(1))
insert [user]
select '001','A' union all
select '002','B'
if object_id('[auth]') is not null drop table [auth]
go
create table [auth]([cauth_id] varchar(6),[cauth_name] varchar(8))
insert [auth]
select '01_001','单据录入' union all
select '01_002','单据审核'
if object_id('[authhold]') is not null drop table [authhold]
go
create table [authhold]([cuser_id] varchar(3),[cauth_id] varchar(6))
insert [authhold]
select '001','01_001'
---查询---
select
a.cuser_id,b.cauth_id,b.cauth_name,
bhold=case when c.cuser_id is null then 0 else 1 end
from [user] a
join [auth] b on 1=1
left join [authhold] c on c.cuser_id=a.cuser_id and c.cauth_id=b.cauth_id---结果---
cuser_id cauth_id cauth_name bhold
-------- -------- ---------- -----------
001 01_001 单据录入 1
001 01_002 单据审核 0
002 01_001 单据录入 0
002 01_002 单据审核 0(所影响的行数为 4 行)