100求sql查询语句解决思路

100求sql查询语句
AFunction表:

ARoleFunction表:


我的sql语句:

Assembly code

select a.FunctionCode as 功能代码,FunctionName as 功能名称,case when b.RoleCode = 'admin'  then 1 else 0 end as Flag from AFunction a left join ARoleFunction b on a.FunctionCode=b.FunctionCodewhere ModuleName='SysAdmin' 

查询出来的结果是:


多了个A001 FrmA001 0 列..
原因是ARoleFunction表RoleCode列的Admin和XG 他们都有A001.我想得到下面的结果要怎么解决？








[解决办法]

SQL code

select a.FunctionCode as 功能代码,a.FunctionName as 功能名称,Flag=case when b.RoleCode = 'admin'  then 1 else 0 endfrom from AFunction a left join ARoleFunction b on a.FunctionCode=b.FunctionCode and b.RoleCode='admin'where a.ModuleName='SysAdmin'
[解决办法]
SQL code
select a.FunctionCode as 功能代码,FunctionName as 功能名称,case when b.RoleCode = 'admin'  then 1 else 0 end as Flag from AFunction a left join ARoleFunction b on a.FunctionCode=b.FunctionCodewhere ModuleName='SysAdmin' and b.RoleCode='admin'
[解决办法]
SQL code
select a.FunctionCode as 功能代码,FunctionName as 功能名称,case when b.RoleCode = 'admin'  then 1 else 0 end as Flag from AFunction a left join ARoleFunction b on a.FunctionCode=b.FunctionCodewhere a.ModuleName='SysAdmin' and b.RoleCode='admin' //这句话应该是要加上去的.前面少了一个a. 

 

查看更多 下一篇