最近老是在搞sql,发现自己差好多。现在我将遇到的问题总结如下:
一。查询申请智能建站管理权限的代理及分公司信息。
现在有四张表:agent_agent:代理表,agent_level:代理级别表,party:公司表,vzzjzgl_apply:智能建站管理权限申请表:
sql语句如下:
select * from (select a.agent_code agent_code, a.user_name user_name, c.name sub_company, d.agent_level || '级' agent_level, ( select x.check_state from vzzjzgl_apply x where x.agent_code = a.agent_code ), ( select xxx.apply_time from vzzjzgl_apply xxx where xxx.agent_code = a.agent_code ), (select xxxx.operator from vzzjzgl_apply xxxx where xxxx.agent_code = a.agent_code ), (select y.id from vzzjzgl_apply y where y.agent_code = a.agent_code ), (select yy.grant_cause from vzzjzgl_apply yy where yy.agent_code = a.agent_code ) from agent_agent a inner join party c on a.organ_code = c.id inner join agent_level d on a.agent_level_code = d.agent_level_code) where 1 = 1 order by agent_code
二。查询申请及未申请智能建站管理权限的代理及分公司信息。
select * from (select a.agent_code agent_code, a.user_name user_name, c.name sub_company, d.agent_level || '级' agent_level, (case when ( select x.check_state from vzzjzgl_apply x where x.agent_code = a.agent_code ) = '01' then trim('已授权') else trim('未授权') end) is_grant, (select xxx.grant_time from vzzjzgl_apply xxx where xxx.agent_code = a.agent_code) operate_time, (case when (select xxxx.operator from vzzjzgl_apply xxxx where xxxx.agent_code = a.agent_code ) is not null then (select xxxx.operator from vzzjzgl_apply xxxx where xxxx.agent_code = a.agent_code) else '---' end ) operator from agent_agent a inner join party c on a.organ_code = c.id inner join agent_level d on a.agent_level_code = d.agent_level_code) where 1 = 1 order by agent_code
说明:
1.注意case when then else end 的使用
2.如果选择一个表的字段为一个列,则要用空号然后将列的名称放在括号的外边:
例如选择 智能建站申请表的审批时间作为整个sql的‘操作时间’字段,如下:
,(select xxx.grant_time from vzzjzgl_apply xxx where xxx.agent_code = a.agent_code) operate_time,