时间戳

首页 > 解决方案 > GROUP BY column HAVING COUNT(*) > 1，仍然显示所有行？

mysql - GROUP BY column HAVING COUNT(*) > 1，仍然显示所有行？

问题描述

我只是想显示具有相同 GROUP_CONCAT() 列值的行。最后使用 GROUP BY 时，由于 GROUP BY 国家/地区，它只显示表中的最后一个全名。当我按国家分组时，是否仍然可以显示所有行？这是我的 sql：

SELECT firstname, lastname, country, COUNT(*) c
FROM (
   SELECT firstname, lastname, 
   GROUP_CONCAT(
      DISTINCT f.countryname
      ORDER BY f.countryname ASC
      SEPARATOR ','
   ) AS country
   FROM person p 
   INNER JOIN favoritecountry f 
   ON p.id = f.id 
   GROUP BY firstname, lastname 
) t 
GROUP BY country 
HAVING c > 1
ORDER BY c DESC;

我的结果是：

+-----------+----------+--------------+---+
| firstname | lastname |   country    | c |
+-----------+----------+--------------+---+
| bill      | smith    | Poland,Spain | 2 |
+-----------+----------+--------------+---+

相反，我想要这样的东西：

+-----------+----------+--------------+---+
| firstname | lastname |   country    | c |
+-----------+----------+--------------+---+
| bill      | smith    | Poland,Spain | 2 |
| phil      | cooper   | Poland,Spain | 2 |
+-----------+----------+--------------+---+

刚接触 SQL，所以需要一些帮助

标签： mysqlgroup-bygroup-concat

解决方案

根据您的预期结果，您应该将 group_concat 国家的计数与国家的名称交叉加入

    select  t2.firstname, t2.lastname, t2.country
    from   (
        SELECT firstname, lastname, 
        GROUP_CONCAT(
          DISTINCT f.countryname
          ORDER BY f.countryname ASC
          SEPARATOR ','
       ) AS country
       FROM person p 
       INNER JOIN favoritecountry f 
       ON p.id = f.id 
       GROUP BY firstname, lastname 
    ) t2
    cross join  (
        select t1.country, count(*) my_count
        from (
            SELECT firstname
             , lastname, 
            GROUP_CONCAT( DISTINCT f.countryname
                ORDER BY f.countryname ASC
                SEPARATOR ','
                ) AS country
            FROM person p 
            INNER JOIN favoritecountry f ON p.id = f.id 
            GROUP BY firstname, lastname 
        )   t 

    ) t1 on t1.country = t2.country

推荐阅读