SQL 按第一个字符分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/666525/
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
group by first character
提问by Pablo Santa Cruz
I have a problem with a query in Oracle SQL.
我在 Oracle SQL 中的查询有问题。
I have a first_namecolumn in an employeestable. I want to group my records according to the first character in first_name.
我first_name在employees表中有一个列。我想根据first_name.
For example, I have 26 records, one with name = 'Alice', one with name = 'Bob', and so on down the alphabet for each name's first character. After the query, there should be 26 groups with one employee each.
例如，我有 26 条记录，一个带有name = 'Alice'，一个带有name = 'Bob'，依此类推，每个名字的第一个字符在字母表中。查询后，应该有 26 个组，每个组有一名员工。
I tried the following, but it's not working:
我尝试了以下方法，但它不起作用：
SELECT employee_id, (SUBSTR(first_name,1,1)) AS alpha FROM employees
GROUP BY alpha;
name_which_starts_from employees
A 10
B 2
C 4
D 9
E 3
G 3
H 3
I 2
J 16
K 7
L 6
M 6
N 4
O 1
P 6
R 3
S 13
T 4
V 2
W 3
回答by Pablo Santa Cruz
Your query is wrong, since you would need to perform some aggregation function on EMPLOYEE_ID if you want that to work.
您的查询是错误的，因为如果您希望它起作用，您需要对 EMPLOYEE_ID 执行一些聚合函数。
Like:
喜欢：
select substr(first_name,1,1) as alpha, count(employee_id)
from employees
group by substr(first_name,1,1)
What exactly you are trying to accomplish?
你到底想完成什么？
回答by yukondude
You'll need to group by everything that is not an aggregate function, so you can't have employee_id in the SELECT projection. You also need to group by just the first character of the first_name. Something like this should work:
您需要按不是聚合函数的所有内容进行分组，因此您不能在 SELECT 投影中使用 employee_id。您还需要仅按 first_name 的第一个字符进行分组。这样的事情应该工作：
SELECT SUBSTR(first_name, 1, 1) AS alpha, COUNT(*) AS employee_count
FROM employees
GROUP BY SUBSTR(first_name, 1, 1);
That would group by the first letter of the first name, and show the number of employees that fall into that group.
这将按名字的第一个字母分组，并显示属于该组的员工人数。
回答by prateeksharma
I have similar issue and solved it this with statement:
我有类似的问题，并用语句解决了这个问题：
select SUBSTR(word, 1, 1) as S, count(word) FROM table_words group by S order by S ASC
select SUBSTR(word, 1, 1) as S, count(word) FROM table_words group by S order by S ASC
回答by Alistair Knock
It almost sounds like you want 26 records returned with A, B, C as the first column and then a second column containing all the employee IDs in a delimited list. If so see question 468990 and/or this Ask Tom link. Something like (untested)
听起来您几乎想要返回 26 条记录，其中 A、B、C 作为第一列，然后第二列包含分隔列表中的所有员工 ID。如果是这样，请参阅问题 468990 和/或此 Ask Tom 链接。类似的东西（未经测试）
SELECT SUBSTR(first_name,1,1), TO_STRING( CAST( COLLECT( employee_id ) AS ntt_varchar2 ) ) AS empIDs
FROM employees
GROUP BY
SUBSTR(first_name,1,1);
回答by Paul Odeon
In Rails/postgres that might look something like this
在 Rails/postgres 中可能看起来像这样
group_clause = 'UPPER(LEFT(name, 1))'
Division.group(group_clause).order(group_clause).pluck(group_clause, 'COUNT(id)')
回答by Joe Suarez
When you are grouping, all of the columns that appear in your select list that are not aggregated have to also appear in the "group by" clause (employee_id does not).
分组时，选择列表中出现的所有未聚合的列也必须出现在“group by”子句中（employee_id 没有）。
Could you clarify what it is you are trying to do?
你能澄清一下你想要做什么吗？
回答by mson
I think i know what you are trying to do...
我想我知道你想做什么...
You should create a small reference table with a column 'letter' (letter, sort_order)
您应该创建一个带有“字母”列（字母、排序顺序）的小型参考表
You should your query as
您应该将查询作为
select l.letter, count(e.id) as employees from letter l left outer join employee e on l.letter = substr(e.first_name, 1,1)
选择 l.letter, count(e.id) 作为员工从字母 l 左外加入员工 e on l.letter = substr(e.first_name, 1,1)
the other answer posted will give you unexpected results when there are no employees with a specific letter in their name...
当没有员工姓名中包含特定字母时，发布的另一个答案会给你意想不到的结果......
