不分组计算 [英] count without group

问题描述

我有一个名为GUYS(ID，NAME，PHONE)的表，我需要添加一个计数来确定有多少个具有相同名称的人，并同时显示所有这些人，因此我无法对它们进行分组. 例如:

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:

ID NAME  PHONE
1  John  335   
2  Harry 444
3  James 367
4  John  742
5  John  654

所需的输出应该是

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.

谢谢

推荐答案

8.0或更高版本的更新:此答案早于MySQL版本8引入，该版本引入了

Update for 8.0+: This answer was written well before MySQL version 8, which introduced window functions with mostly the same syntax as the existing ones in 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.

较旧的版本:从MySQL开始，直到版本8都没有

Older versions: Since MySQL, until version 8, didn't have analytical functions like Oracle, you'd have to resort to a sub-query.

不要使用GROUP BY，请使用子选择来计算具有相同名称的人的数量:

Don't use GROUP BY, use a sub-select to count the number of guys with the same name:

SELECT
  t.name,
  t.phone,
  (SELECT COUNT('x') FROM Guys ct 
   WHERE ct.name = t.name) as namecounter
FROM
  Guys t

您可能会认为为每行运行一个子选择会很慢，但是如果您有正确的索引，MySQL会优化该查询，并且您会发现它运行得很好.

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.

在此示例中，您应该在Guys.name上具有索引.如果子查询的where子句中有多个列，则该查询可能会受益于所有这些列上的单个组合索引.

In this example, you should have an index on Guys.name. If you have multiple columns in the where clause of the subquery, the query would probably benefit from a single combined index on all of those columns.

这篇关于不分组计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！