oracle SQL Group By 函数（列） - 现在无法选择该列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6135213/
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
SQL Group By function(column) - Now can't Select that column
提问by Mark Estrada
I used the HR employee schema in Oracle Express and I wanted to select employees that were hired on a particular year.
我在 Oracle Express 中使用了 HR 员工模式，我想选择特定年份雇用的员工。
SELECT hire_date,
COUNT(*)
FROM employees empl
GROUP BY SUBSTR(hire_date, -4)
ORDER BY empl.hire_date;
The hire_date column has this format "1/1/2011" so I'd like to group them by extracting the last four char.
hiring_date 列的格式为“1/1/2011”，因此我想通过提取最后四个字符对它们进行分组。
Problem is, I am encountering below error
问题是，我遇到以下错误
ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action:
Error at Line: 1 Column: 7
Is this not possible?
这不可能吗？
回答by paxdiablo
You can't select the full hire_dateif you're groupingby only the last four digits of it. Think of what will happen if you have the two rows:
hire_date如果仅按最后四位数字分组，则无法选择完整数字。想想如果你有两行会发生什么：
hire_date
=========
01/01/2001
02/02/2001
In the singlerow generated when you group those, what should the hire_datebe?
在将它们分组时生成的单行中，应该hire_date是什么？
Every column selected must either be a group-by column or an aggregate column. In other words, try:
所选的每一列都必须是分组列或聚合列。换句话说，尝试：
select substr(hire_date,-4), count(*)
from employees
group by substr(hire_date,-4)
order by empl.hire_date;
I should mention that per-row functions are notoriously bad at scaling. If you want to process the year a lot, you should consider splitting it into its own column. That may greatly improve performance, but measure, don't guess!
我应该提到每行函数在缩放方面是出了名的糟糕。如果要大量处理年份，则应考虑将其拆分为单独的列。这可能会大大提高性能，但要衡量，不要猜测！
And, as others have mentioned in comments, substris probably not the best solution since that may depend on locale (as in: it may be possible for the date to be formatted as YYYY-MM-DDwhich will not go well with the substring).
而且，正如其他人在评论中提到的那样，substr可能不是最好的解决方案，因为这可能取决于语言环境（例如：日期可能被格式化为YYYY-MM-DD与substring.不符的格式）。
It may be better to use something like to_char(hire_date,'YYYY')or extract (year from hire_date)which should be more robust.
使用类似to_char(hire_date,'YYYY')或extract (year from hire_date)应该更健壮的东西可能会更好。
回答by schurik
you can also truncate the hiredate column
你也可以截断被雇佣的列
select trunc(hiredate, 'yyyy'), count(*)
from employee
group by trunc(hiredate, 'yyyy')
回答by josephj1989
if you want group employees by the year they were hired in use
如果您希望按被雇用的年份对员工进行分组
select to_char(hiredate,'yyyy'),count(*)
from employee
group by to_char(hiredate,'yyyy')
回答by Tommi
You can only use GROUP BYconditions or aggregate functions (MIN, MAX, AVG, etc.) in the SELECTpart of a GROUP BYquery. This works:
您只能在查询部分使用GROUP BY条件或聚合函数（MIN, MAX, AVG等）。这有效：SELECTGROUP BY
select substr(hire_date,-4), count(*)
from employees empl
group by substr(hire_date,-4)
order by substr(hire_date,-4);
