CASE语句是否会丢失PostgreSQL中的别名范围？

发布时间：2020-12-13 15:58:35 所属栏目：百科来源：网络整理

导读：首先,这个SQL有效： select case when s.luserid 0 then u.szusername when s.lgroupid 0 then g.szgroup when s.lldapid 0 then 'LDAP Group' end as namefrom security sleft join users u on s.luserid = u.idleft join usergroups g on s.lgroupid = g.i

首先,这个SQL有效：

select
    case
        when s.luserid > 0 then u.szusername
        when s.lgroupid > 0 then g.szgroup
        when s.lldapid > 0 then 'LDAP Group'
    end as name
from security s
left join users u on s.luserid = u.id
left join usergroups g on s.lgroupid = g.id
order by name

上面的块通过别名工作证明排序,以及声明别名名称有效,而术语名称是保留字,这与问题无关

当我在其中创建带有别名的case语句时,我的问题就出现了：

注意别名useid

select
    case
        when sa.luserid > 0 then sa.luserid
        when sa.lgroupid > 0 then sa.lgroupid
        when sa.lldapid > 0 then sa.lldapid
    end as useid,from security s
left join users u on s.luserid = u.id
left join usergroups g on s.lgroupid = g.id
order by
        case
            when 'user selection' = 'all objects by user' then useid
            else s.lobjectid
        end

在运行SQL之前,文本用户选择由具有文字文本的解析器替换.别名useid和s.lobjectid都是bigint类型.

当’用户选择’=’用户的所有对象’然后使用useid时,会引发错误.

我是否在CASE声明中丢失了别名useid的范围？
当我尝试在此处使用别名useid时,为什么会失败.

顺便说一句,这个SQL也可以：

select
    case
        when s.luserid > 0 then u.szusername
        when s.lgroupid > 0 then g.szgroup
        when s.lldapid > 0 then 'LDAP Group'
    end as name
from security s
left join users u on s.luserid = u.id
left join usergroups g on s.lgroupid = g.id
order by
        case
            when s.lobjectid > 0 then s.lobjectid
            else s.luserid
        end

上面的块证明了ORDER BY语句中的CASE语句确实有效.关于上述SQL块的逻辑操作的所有争论都与问题无关,因为它只是垃圾示例SQL.

解决方法

在Postgresql中你不能尝试做什么,因为它不允许你在同一个查询中使用ALIAS作为字段.与Mysql不同,你可以在那里做到.

要解决您的问题,您可以将查询创建为子查询,然后您的别名将是一个字段,因此可以用作：

select useid,lobjectid from (
  select
      case
          when sa.luserid > 0 then sa.luserid
          when sa.lgroupid > 0 then sa.lgroupid
          when sa.lldapid > 0 then sa.lldapid
      end as useid,lobjectid
   from security s
  left join users u on s.luserid = u.id
  left join usergroups g on s.lgroupid = g.id
  ) as t
order by
        case
            when 'user selection' = 'all objects by user' then useid
            else lobjectid
        end

或者你可以重复entiry case块

select
      case
          when sa.luserid > 0 then sa.luserid
          when sa.lgroupid > 0 then sa.lgroupid
          when sa.lldapid > 0 then sa.lldapid
      end as useid,lobjectid
   from security s
  left join users u on s.luserid = u.id
  left join usergroups g on s.lgroupid = g.id
order by
        case
            when 'user selection' = 'all objects by user' then 
                  case
                      when sa.luserid > 0 then sa.luserid
                      when sa.lgroupid > 0 then sa.lgroupid
                      when sa.lldapid > 0 then sa.lldapid
                  end
            else lobjectid
        end

某些引擎会让您使用选择范围上的字段的订单号,顺序如下：

select a,b,c from sometable order by 1,2

这意味着此查询将按字段a和b排序

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!