如何在 PL/SQL 中按每天分组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10123202/
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 to group by each day in PL/SQL?
提问by Jason94
Im trying to get statistics for each day with PL/SQL.
我正在尝试使用 PL/SQL 获取每天的统计信息。
Each day have several entries, bukkets of errors :-) I want to group them by day.
每天都有几个条目，错误的 bukkets :-) 我想按天对它们进行分组。
What Im currently doing:
我目前在做什么：
SELECT TO_CHAR(dateTime, 'DD') DAY, TO_CHAR(dateTime, 'MM') MONTH, errormessage
FROM log
WHERE (...)
GROUP BY MONTH, DAY, errormessage
This results in ORA-00904: "DAY": invalid identifier (stops on the group by).
这导致 ORA-00904: "DAY": invalid identifier (stops on the group by)。
Any help? :D
有什么帮助吗？:D
回答by Erkan Haspulat
SELECT TO_CHAR(dateTime, 'DD') DAY, TO_CHAR(dateTime, 'MM') MONTH, errormessage
FROM log
WHERE (...)
GROUP BY TO_CHAR(dateTime, 'DD'), TO_CHAR(dateTime, 'MM'), errormessage
Column aliases are no good for GROUP BY, you need the full expression.
列别名不适用于GROUP BY，您需要完整的表达式。
回答by Lukas Eder
Your problem is a scope problem. The GROUP BYclause is part of the statement's table expression and is thus evaluated before the SELECTclause, i.e. the projection. This means that your projected aliases are not available at grouping time. This is one solution
你的问题是范围问题。该GROUP BY子句是语句表表达式的一部分，因此在该SELECT子句（即投影）之前进行评估。这意味着您的预计别名在分组时不可用。这是一种解决方案
SELECT DAY, MONTH, errormessage
FROM (
SELECT TO_CHAR(dateTime, 'DD') DAY, TO_CHAR(dateTime, 'MM') MONTH, errormessage
FROM log
WHERE (...)
)
GROUP BY MONTH, DAY, errormessage
this is another:
这是另一个：
SELECT TO_CHAR(dateTime, 'DD') DAY, TO_CHAR(dateTime, 'MM') MONTH, errormessage
FROM log
WHERE (...)
GROUP BY TO_CHAR(dateTime, 'MM'), TO_CHAR(dateTime, 'DD'), errormessage
Non-standard behaviour
非标准行为
Note that some databases (including MySQL, PostgreSQL, and others) do allow to reference column aliases from the projection in the GROUP BYclause. In stricter SQL dialects (such as Oracle) that should not be possible, though
请注意，某些数据库（包括 MySQL、PostgreSQL 和其他数据库）确实允许从GROUP BY子句中的投影中引用列别名。在更严格的 SQL 方言（例如 Oracle）中，这是不可能的，但是
回答by Rapha?l Althaus
SELECT TO_CHAR(dateTime, 'DD') DAY, TO_CHAR(dateTime, 'MM') MONTH, errormessage
FROM log
WHERE (...)
GROUP by TO_CHAR(dateTime, 'DD'), TO_CHAR(dateTime, 'MM'), errormessage
you can't use aliases in group by
您不能在 group by 中使用别名
