MySQL 不分组计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4259611/
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
count without group
提问by east
I have one table named GUYS(ID,NAME,PHONE) and i need to add a count of how many guys have the same name and at the same time show all of them so i can't group them. example:
我有一个名为 GUYS(ID,NAME,PHONE) 的表，我需要计算有多少人具有相同的名字，并同时显示所有这些人，因此我无法将他们分组。例子：
ID NAME PHONE
1 John 335
2 Harry 444
3 James 367
4 John 742
5 John 654
the wanted output should be
想要的输出应该是
ID NAME PHONE COUNT
1 John 335 3
2 Harry 444 1
3 James 367 1
4 John 742 3
5 John 654 3
how could i do that? i only manage to get lot of guys with different counts.
我怎么能那样做？我只能设法得到很多不同数量的人。
thanks
谢谢
回答by GolezTrol
Update for 8.0+: This answer was written well before MySQL version 8, which introduced window functionswith mostly the same syntax as the existing ones in Oracle.
8.0+ 的更新：这个答案是在 MySQL 版本 8 之前编写的，它引入了与 Oracle 中现有的语法几乎相同的窗口函数。
In this new syntax, the solution would be
在这种新语法中，解决方案是
SELECT
t.name,
t.phone,
COUNT('x') OVER (PARTITION BY t.name) AS namecounter
FROM
Guys t
The answer below still works on newer versions as well, and in this particular case is just as simple, but depending on the circumstances, these window functions are way easier to use.
下面的答案仍然适用于较新的版本，在这种特殊情况下同样简单，但根据情况，这些窗口函数更易于使用。
Older versions: Since MySQL, until version 8, didn't have analytical functions like Oracle, you'd have to resort to a sub-query.
旧版本：由于 MySQL 在版本 8 之前没有像 Oracle这样的分析功能，因此您必须求助于子查询。
Don't use GROUP BY, use a sub-select to count the number of guys with the same name:
不要使用GROUP BY，使用子选择来计算同名的人数：
SELECT
t.name,
t.phone,
(SELECT COUNT('x') FROM Guys ct
WHERE ct.name = t.name) as namecounter
FROM
Guys t
You'd think that running a sub-select for every row would be slow, but if you've got proper indexes, MySQL will optimize this query and you'll see that it runs just fine.
您可能认为为每一行运行一个子选择会很慢，但如果您有适当的索引，MySQL 将优化此查询，您会看到它运行得很好。
In this example, you should have an index on Guys.name. If you have multiple columns in the whereclause of the subquery, the query would probably benefit from a single combined index on all of those columns.
在此示例中，您应该在 上有一个索引Guys.name。如果where子查询的子句中有多个列，则查询可能会受益于所有这些列的单个组合索引。
回答by Sowmia Naraynan
Use an aggregate Query:
使用聚合查询：
select g.ID, g.Name, g.Phone, count(*) over ( partition by g.name ) as Count
from
Guys g;
回答by RedFilter
You can still use a GROUP BYfor the count, you just need to JOINit back to your original table to get all the records, like this:
您仍然可以使用 aGROUP BY进行计数，您只需要将JOIN其返回到原始表以获取所有记录，如下所示：
select g.ID, g.Name, g.Phone, gc.Count
from Guys g
inner join (
select Name, count(*) as Count
from Guys
group by Name
) gc on g.Name = gc.Name
回答by Sayantan Dey
In Oracle DB you can use
在 Oracle DB 中，您可以使用
SELECT ID,NAME,PHONE,(Select COUNT(ID)From GUYS GROUP BY Name)
FROM GUYS ;
SELECT ID,NAME,PHONE,(Select COUNT(ID)From GUYS GROUP BY Name)
FROM GUYS ;
回答by redoc
select id, name, phone,(select count(name) from users u1 where u1.name=u2.name) count from users u2
