MySQL：GROUP_CONCAT与ORDER BY COUNT？ [英] MySQL: GROUP_CONCAT with an ORDER BY COUNT?

问题描述

这是否可能？

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

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.

首先我通过邮政编码和电话进行GROUP BY。

First I do a GROUP BY on postalcode and telephone.

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

$ b b

但是我得到随机名称。我想获得最好的名称，即每个邮政编码/电话中条目最多的名称。

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.

以下。这里我使用SUBSTRING_INDEX函数只获取group_concat中的第一个项目（没有名称带有字符串'~~'）：

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'.

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

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

推荐答案

使用子查询找到一个解决方案：

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.

这篇关于MySQL：GROUP_CONCAT与ORDER BY COUNT？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！