SQL 如何在 Group By 中使用 Case Statement 列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20245218/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How Do I Use Case Statement Column In Group By
提问by butallmj
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:
正如问题所述，我试图制定一个在列结果中包含 case 语句的查询，然后我想将该列包含在查询的 group by 语句中。举一个具体的例子，这里是我的查询的全部内容：
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
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 CASEkeyword like is shown in the linked web page but have had no luck. The error message I continue to receive is the following:
在来这里之前，我查看了一些网站，包括这个，以尝试解决我的问题。我尝试CASE在链接的网页中显示关键字 like之后添加另一个别名，但没有成功。我继续收到的错误消息如下：
[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
Has anyone else run into the issues I'm facing and been able to use a GROUP BYon the results of a CASEstatement? Any help would be appreciated. Oh, and the DB2 version is a z/OS instance, version 10 (DSN10015)
有没有其他人遇到过我面临的问题并且能够对语句GROUP BY的结果使用 a CASE？任何帮助，将不胜感激。哦，DB2 版本是 z/OS 实例，版本 10 (DSN10015)
回答by Filipe Silva
The alias isn't available to use in the GROUP BY because when GROUP BY happens the alias isn't defined yet:
别名在 GROUP BY 中不可用，因为当 GROUP BY 发生时，别名尚未定义：
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
Or just use the case you use in SELECT in GROUP BY
或者只是使用您在 SELECT 中使用的案例在 GROUP BY
回答by RedFilter
You can either use the case as is in the group by, like this:
您可以在 group by 中按原样使用 case，如下所示：
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
WHEN SOME_TABLE_ALIAS.COLUMN3 IS NOT NULL THEN 'A'
ELSE 'B'
END
or use a sub-query like this:
或使用这样的子查询：
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
