sql简单总结-CSDN博客

本文链接：https://blog.csdn.net/tangtlaoda/article/details/83838424

[size=medium]最近老是在搞sql，发现自己差好多。现在我将遇到的问题总结如下：[/size]

[size=large]一。查询申请智能建站管理权限的代理及分公司信息。[/size]
[size=medium]现在有四张表：agent_agent：代理表，agent_level:代理级别表，party：公司表，vzzjzgl_apply：智能建站管理权限申请表：
sql语句如下：[/size]


 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

[size=large]二。查询申请及未申请智能建站管理权限的代理及分公司信息。[/size]


   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

[size=medium]说明：
1.注意case when then else end 的使用
2.如果选择一个表的字段为一个列，则要用空号然后将列的名称放在括号的外边：
例如选择智能建站申请表的审批时间作为整个sql的‘操作时间’字段，如下：[/size]


  ,(select xxx.grant_time
                         from vzzjzgl_apply xxx
                        where xxx.agent_code = a.agent_code)  operate_time,