首先,这个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排序
