在MySQL中一起使用Distinct，Count和Group

G Delaney 发表于 Dev

德莱尼

我有一个“吸引力”数据库表，其中每个景点的县和/或城市都有列。我需要按字母顺序显示县和城市的列表，以及每个县或城市的记录总数。

下面的SQL查询工作正常，除了COUNT总共只显示一个，而不是每个县或城市的景点总数。

谁能告诉我如何将下面的两个SQL语句组合在一起？

SQL：

   SELECT entry, COUNT(entry) AS totalForEntry 
   FROM ( SELECT DISTINCT county AS entry
          FROM venues 
          WHERE (county  IS NOT NULL AND county <> '' ) 

          UNION ALL 

          SELECT DISTINCT city as entry
          FROM venues
          WHERE (city IS NOT NULL 
                 AND
                 city  <> ''  
                 )
      ) X GROUP BY entry ORDER BY entry ASC

结果：

Hove (1)
Inverness-shire (1)
Isle of Man (1)
Kent (1)
Lancashire (1)
Leeds (1)
Leicester (1)
Leicestershire (2)
Lincolnshire (1)
Liverpool (1)

以下是对县进行计数的方法，我也需要对城市进行计数，并将其与上面的查询结合起来：

SELECT DISTINCT county, COUNT(county) AS Count
FROM venues
WHERE (county IS NOT NULL AND county <> '') 
GROUP BY county ORDER BY county ASC

正确的结果应该是这样的：

Hove (7)
Inverness-shire (3)
Isle of Man (12)
Kent (20)
Lancashire (34)
Leeds (31)
Leicester (5)
Leicestershire (53)
Lincolnshire (7)
Liverpool (43)

提前谢谢了。

乔治·贝索斯（Giorgos Betsos）

它没有意义的使用DISTINCT与GROUP BY。你可以做：

SELECT county AS entry, COUNT(county) AS Count
FROM venues
WHERE (county IS NOT NULL AND county <> '') 
GROUP BY county 

UNION ALL 

SELECT city as entry, COUNT(city) As Count
FROM venues
WHERE (city IS NOT NULL AND city  <> '')
GROUP BY city 
ORDER BY entry ASC

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。