如何在 Group By 中使用 Case 语句列 [英] How Do I Use Case Statement Column In Group By

问题描述

正如问题所述，我正在尝试制定一个在列结果中包含 case 语句的查询，然后我想将该列包含在查询的 group by 语句中.举一个具体的例子，下面是我的查询的全部内容:

As stated by the question, I'm trying to formulate a query that has a case statement in the column results, and then I want to include that column in the query's group by statement. To give a concrete example, here is all little of what my query looks like:

SELECT SOME_TABLE_ALIAS.COLUMN1, OTHER_TABLE_ALIAS.COLUMN2,
CASE
    WHEN SOME_TABLE_ALIAS.COLUMN3 IS NOT NULL THEN 'A'
    ELSE 'B'
END AS CASE_COLUMN
FROM SOME_TABLE SOME_TABLE_ALIAS
... (other table joins and where clauses)
GROUP BY SOME_TABLE_ALIAS.COLUMN1, OTHER_TABLE_ALIAS.COLUMN2, CASE_COLUMN

来这里之前，我查看了几个网站，包括 这个，尝试解决我的问题.我尝试在链接网页中显示的 CASE 关键字之后添加另一个别名，但没有运气.我继续收到的错误消息如下:

Before coming here, I checked out a few websites, including this one, to try solve my problem. I've tried adding another alias after the CASE keyword like is shown in the linked web page but have had no luck. The error message I continue to receive is the following:

[Error] Script lines: 127-151 ----------------------
 CASE_COLUMN IS NOT VALID IN THE CONTEXT WHERE IT IS USED. SQLCODE=-206, SQLSTATE=42703, DRIVER=3.53.71 

有没有其他人遇到过我面临的问题并且能够在 CASE 语句的结果中使用 GROUP BY?任何帮助，将不胜感激.哦，DB2 版本是 z/OS 实例，版本 10 (DSN10015)

Has anyone else run into the issues I'm facing and been able to use a GROUP BY on the results of a CASE statement? Any help would be appreciated. Oh, and the DB2 version is a z/OS instance, version 10 (DSN10015)

推荐答案

别名不能在 GROUP BY 中使用，因为当 GROUP BY 发生时，别名尚未定义:

The alias isn't available to use in the GROUP BY because when GROUP BY happens the alias isn't defined yet:

Here's the order:
1.FROM
2.WHERE
3.GROUP BY
4.HAVING
5.SELECT
6.ORDER BY

您可以通过以下方式解决这个问题:

You can work around that with:

SELECT column1,column2,case_column
FROM (
SELECT SOME_TABLE_ALIAS.COLUMN1, OTHER_TABLE_ALIAS.COLUMN2,
CASE
    WHEN SOME_TABLE_ALIAS.COLUMN3 IS NOT NULL THEN 'A'
    ELSE 'B'
END AS CASE_COLUMN
FROM SOME_TABLE SOME_TABLE_ALIAS
... (other table joins and where clauses)
) a
GROUP BY COLUMN1, COLUMN2, CASE_COLUMN

或者只使用您在 SELECT in GROUP BY 中使用的大小写

Or just use the case you use in SELECT in GROUP BY

这篇关于如何在 Group By 中使用 Case 语句列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！