mysql - 2 个嵌套的 COUNT 和 GROUPBY 语句上的 SQL JOIN 错误

Question

我正在尝试合并 2 个查询，一个计算每个国家/地区的用户，另一个计算每个国家/地区使用公用电话的用户并将列合并在一起。但是，我在内部连接代码区域附近不断收到错误消息。我正在使用 HeidiSQL，所以这是我收到的唯一错误消息。每个嵌套查询都可以完美地独立运行。有谁知道怎么回事吗？

SELECT
   AllUsers.country, 
   AllUsers.TotalUsers, 
   PublicUsers.PublicPhoneUsers
FROM

   (SELECT 
      country, 
      count(country) As "TotalUsers" 
   FROM 
      userlist
   GROUP BY 
      country) AS "AllUsers"

INNER JOIN

   (SELECT 
      country, 
      count(country) As "PublicPhoneUsers"
   FROM 
      userlist 
   WHERE 
      phone_public = 1 
   GROUP BY 
      country) AS "PublicUsers"

ON
   AllUsers.country = PublicUsers.country;

Answer 1

请从您的列和表别名中删除双引号:

SELECT AllUsers.country, AllUsers.TotalUsers, PublicUsers.PublicPhoneUsers
FROM
(
    SELECT country, COUNT(country) As TotalUsers
    FROM userlist
    GROUP BY country
) AS AllUsers
INNER JOIN
(
    SELECT country, COUNT(country) As PublicPhoneUsers
    FROM userlist 
    WHERE phone_public = 1 
    GROUP BY country
) AS PublicUsers
ON AllUsers.country = PublicUsers.country