MySQL：带有 ORDER BY COUNT 的 GROUP_CONCAT？

发布于 2024-11-30 15:33:33 字数 691 浏览 1 评论 0原文

这可能吗？

假设我有一个地址列表，其中有很多重复的条目。我需要过滤掉所有重复项，因为有些地址的名称略有不同，但邮政编码和电话号码相同。

首先，我对邮政编码和电话进行 GROUP BY。

SELECT name, address, postalcode, place, phone
FROM addresses
GROUP BY postalcode, phone

但后来我得到了随机的名字。我想获得最佳名称，即每个邮政编码/电话条目最多的名称。

所以我想到了以下几点。在这里，我使用 SUBSTRING_INDEX 函数仅获取 group_concat 中的第一项（其中没有带有字符串“~~”的名称）：

SELECT SUBSTRING_INDEX(
         GROUP_CONCAT(DISTINCT name ORDER BY COUNT(name) DESC SEPARATOR '~~')
       , '~~', 1),
       address,
       postalcode,
       place,
       phone
FROM addresses
GROUP BY postalcode, telephone

但我得到“组函数的无效使用”。

如何让 GROUP_CONCAT 按名称出现的次数排序？

原文

Is this possible ?

Let's say I have a list of addresses, with a lot of duplicate entries. I need to filter out all the duplicates, because some addresses have slightly different names but the same postalcode and telephone number.

First I do a GROUP BY on postalcode and telephone.

SELECT name, address, postalcode, place, phone
FROM addresses
GROUP BY postalcode, phone

But then I get random names. I would like to get the best name, that is, the name with the most entries per postalcode/phone.

So I thought of the following. Here I use the SUBSTRING_INDEX function to only get the first item in the group_concat (there are no names with the string '~~' in it):

SELECT SUBSTRING_INDEX(
         GROUP_CONCAT(DISTINCT name ORDER BY COUNT(name) DESC SEPARATOR '~~')
       , '~~', 1),
       address,
       postalcode,
       place,
       phone
FROM addresses
GROUP BY postalcode, telephone

but I get an 'invalid use of group function'.

How do I get the GROUP_CONCAT to order by the number of times the name occurs ?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

-小熊_ 2024-12-07 15:33:33

我自己找到了一个带有子查询的解决方案：

SELECT 
  SUBSTRING_INDEX(
    GROUP_CONCAT(DISTINCT name ORDER BY CountName DESC SEPARATOR '||')
  , '||', 1),
  address,
  postalcode,
  place,
  phone
FROM (

  SELECT name, address, postalcode, place, phone , COUNT(name) AS CountName
  FROM addresses
  GROUP BY name, postalcode, phone
  ORDER BY COUNT(name) DESC

) as a
GROUP BY postalcode, phone

我想知道是否可以在没有子查询的情况下完成。

Found a solution myself, with a subquery:

SELECT 
  SUBSTRING_INDEX(
    GROUP_CONCAT(DISTINCT name ORDER BY CountName DESC SEPARATOR '||')
  , '||', 1),
  address,
  postalcode,
  place,
  phone
FROM (

  SELECT name, address, postalcode, place, phone , COUNT(name) AS CountName
  FROM addresses
  GROUP BY name, postalcode, phone
  ORDER BY COUNT(name) DESC

) as a
GROUP BY postalcode, phone

I wonder if it can be done without a subquery.

回复收藏 0 原文

~没有更多了~