表cms_popedeom
pw_code pw_name
cms00001 权限1
cms00002 权限2
cms00003 权限3
表cms_role_popedomlk_code rl_code pw_code
1 1 cms00001
2 1 cms00002
3 1 cms00002
4 2 cms00002
5 2 cms00002想形成一下的结果:
rl_code pw_code pw_name ised
1 cms00001 权限1 1
1 cms00002 权限2 1
1 cms00003 权限3 0说明:结果显示的是表cms_popedeom所有的字段,如果表cms_role_popedom中rl_code为1的数据用到了相应的pw_code ,则ised显示为1,否则显示为0
pw_code pw_name
cms00001 权限1
cms00002 权限2
cms00003 权限3
表cms_role_popedomlk_code rl_code pw_code
1 1 cms00001
2 1 cms00002
3 1 cms00002
4 2 cms00002
5 2 cms00002想形成一下的结果:
rl_code pw_code pw_name ised
1 cms00001 权限1 1
1 cms00002 权限2 1
1 cms00003 权限3 0说明:结果显示的是表cms_popedeom所有的字段,如果表cms_role_popedom中rl_code为1的数据用到了相应的pw_code ,则ised显示为1,否则显示为0
left join cms_role_popedom b on a.pw_code = b.pw_code
from
( select p.pw_code,p.pw_name,p1.rl_code
from cms_popedeom p , (select distinct rl_code from cms_role_popedom) p1) p2 left join
( select rl_code,pw_code,1 as ised ,count(*)
from cms_role_popedom
group by rl_code,pw_code ) r
on p2.pw_code=r.pw_code and p2.rl_code=r.rl_code
insert into cms_popedeom values('cms00001', '权限1')
insert into cms_popedeom values('cms00002', '权限2')
insert into cms_popedeom values('cms00003', '权限3')
create table cms_role_popedom(lk_code int, rl_code int , pw_code varchar(10))
insert into cms_role_popedom values(1, 1, 'cms00001')
insert into cms_role_popedom values(2, 1, 'cms00002')
insert into cms_role_popedom values(3, 1, 'cms00002')
insert into cms_role_popedom values(4, 2, 'cms00002')
insert into cms_role_popedom values(5, 2, 'cms00002')
goselect rl_code = 1 , * , ised = 1 from cms_popedeom where pw_code in (select pw_code from cms_role_popedom where rl_code = 1)
union all
select rl_code = 1 , * , ised = 0 from cms_popedeom where pw_code not in (select pw_code from cms_role_popedom where rl_code = 1)drop table cms_popedeom,cms_role_popedom/*
rl_code pw_code pw_name ised
----------- ---------- ---------- -----------
1 cms00001 权限1 1
1 cms00002 权限2 1
1 cms00003 权限3 0(所影响的行数为 3 行)
*/
----------------------------------------------
服务器: 消息 8155,级别 16,状态 2,行 1
没有为第 4 列(属于 'r')指定列。
insert into cms_popedeom values('cms00001', '权限1')
insert into cms_popedeom values('cms00002', '权限2')
insert into cms_popedeom values('cms00003', '权限3')
create table cms_role_popedom(lk_code int, rl_code int , pw_code varchar(10))
insert into cms_role_popedom values(1, 1, 'cms00001')
insert into cms_role_popedom values(2, 1, 'cms00002')
insert into cms_role_popedom values(3, 1, 'cms00002')
insert into cms_role_popedom values(4, 2, 'cms00002')
insert into cms_role_popedom values(5, 2, 'cms00002')
go
select isnull(b.rl_code,1) as rl_code,
a.pw_code,a.pw_name,
case when b.rl_code = 1 then 1 else 0 end as ised
from cms_popedeom a
left join (select min(rl_code) as rl_code,pw_code
from cms_role_popedom
group by pw_code) b on a.pw_code = b.pw_codedrop table cms_popedeom,cms_role_popedom/*
rl_code pw_code pw_name ised
----------- ---------- ---------- -----------
1 cms00001 权限1 1
1 cms00002 权限2 1
1 cms00003 权限3 0(所影响的行数为 3 行)
*/
if object_id('cms_popedeom ') is not null
drop table cms_popedeom
if object_id('cms_role_popedom ') is not null
drop table cms_role_popedomgocreate table cms_popedeom(pw_code varchar(20) not null primary key,pw_name varchar(20) not null)
create table cms_role_popedom(lk_code int not null primary key,rl_code int not null,pw_code varchar(20) not null)
goinsert cms_popedeom
select 'cms00001','權限1' union all
select 'cms00002','權限2' union all
select 'cms00003','權限3' insert cms_role_popedom
select 1,1,'cms00001' union all
select 2,1,'cms00002' union all
select 3,1,'cms00002' union all
select 4,2,'cms00002' union all
select 5,2,'cms00002'
goselect * from cms_popedeom
/*
pw_code pw_name
cms00001 权限1
cms00002 权限2
cms00003 权限3
*/
select * from cms_role_popedom
/*
lk_code rl_code pw_code
1 1 cms00001
2 1 cms00002
3 1 cms00002
4 2 cms00002
5 2 cms00002
*/--方法一:
select '1' as rl_code,*,ised =case when exists(select 1 from cms_role_popedom where a.pw_code=pw_code and rl_code=1) then 1 else 0 end from cms_popedeom a
/*
rl_code pw_code pw_name ised
1 cms00001 权限1 1
1 cms00002 权限2 1
1 cms00003 权限3 0
*/--方法二:
select '1' as rl_code,a.*,case when b.pw_code is null then 0 else 1 end ised from cms_popedeom a left join (select distinct pw_code from cms_role_popedom where rl_code=1) b on a.pw_code=b.pw_code
/*
rl_code pw_code pw_name ised
1 cms00001 权限1 1
1 cms00002 权限2 1
1 cms00003 权限3 0
*/
a.pw_code,a.pw_name,
case when b.rl_code = 1 then 1 else 0 end as ised
from cms_popedeom a
left join (select min(rl_code) as rl_code,pw_code
from cms_role_popedom
group by pw_code) b on a.pw_code = b.pw_code