sql按大小写分组条件在选择中重复

4zcjmb1e 于 2021-06-25 发布在 Hive

关注(0)|答案(1)|浏览(263)

sql语句：

select (case when order_id != 0 then order_id end) as order_id,
   (case when order_id = 0 then other_field end) as other_field,
   count(*)
from table t
group by (case when order_id != 0 then order_id end),
     (case when order_id = 0 then other_field end);

案件情况 (case when order_id != 0 then order_id end) 以及 (case when order_id = 0 then other_field end) 必须在select查询中重复，并且我已尝试添加 as field 按条件分组，如

select order_id,
   other_field,
   count(*)
from table t
group by (case when order_id != 0 then order_id end) as order_id,
     (case when order_id = 0 then other_field end) as order_field;

但它显示错误，是否可以使用 case 条件一次？因为也许情况会变得太长而无法理解。

sql Hive

来源：https://stackoverflow.com/questions/60541494/sql-group-by-case-condition-repeat-in-select

1条答案

按热度按时间

sirbozc51#

这是绝对可能的，在这种情况下，您必须重复case语句（或者在子查询中使用别名，然后通过别名引用它们以避免重复），我已经做过很多次了。但是1）你不能在第2部分的分组中使用别名2）你的case语句似乎语法错误，因为它们缺少else部分。
试着这样做：

select (case when order_id != 0 then order_id else NULL end) as order_id,
   (case when order_id = 0 then other_field ELSE NULL end) as other_field,
   count(*)
from table t
group by case when order_id != 0 then order_id else NULL end,
     case when order_id = 0 then other_field ELSE NULL end;

为了避免重复，您可以这样做：

select order_id, other_field,
           count(*)
        from (
             select (case when order_id != 0 then order_id else NULL end) as order_id,
                    (case when order_id = 0 then other_field ELSE NULL end) as other_field     from table 
            )t
        group by order_id, other_field ;

编辑：正如gordon linoff所说，else部分毕竟不是必需的，所以您可以只使用以下部分：

select order_id, other_field,
           count(*)
        from (
             select (case when order_id != 0 then order_id end) as order_id,
                    (case when order_id = 0 then other_field E end) as other_field     from table 
            )t
        group by order_id, other_field ;

赞(0）回复(0）举报 2021-06-26

我来回答

sql按大小写分组条件在选择中重复

1条答案

相关问题

热门标签

最新问答