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.IDorder by name
上面的块通过别名工作z明排序,以及声明别名名称有效,而术语名称是保留字,这与问题无关
当我在其中创建带有别名的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 sleft join users u on s.luserID = u.IDleft join usergroups g on s.lgroupID = g.IDorder 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 namefrom security sleft join users u on s.luserID = u.IDleft join usergroups g on s.lgroupID = g.IDorder 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 torder 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.IDorder 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排序
@H_301_66@ 总结以上是内存溢出为你收集整理的CASE语句是否会丢失PostgreSQL中的别名范围?全部内容,希望文章能够帮你解决CASE语句是否会丢失PostgreSQL中的别名范围?所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)