总数和细目

Question

我想知道有多少用户来自于该城市内的哪个城市和行政区/社区。因此，我正在寻找一个查询，它将输出这两个查询的计数结果。我有一个有用户数据的表，没有特定的城市和行政区/社区，但是每个用户都有一个邮政编码。这可以用来匹配表上的邮政编码，它包含城市和社区的数据。

表用户

+----+--------------------+--------------+
| ID | User               | Postcode     |
+----+--------------------+--------------+
| 10 | John Doe           | 1100—99-AB   | 
| 11 | Shara Lee          | 1201—34-CD   |
| 12 | Patrick Star       | 1100—99-AB   |
| 13 | Oswald Harvey      | 1100—99-AB   |
| 14 | Samuel Jackson     | 1401—34-TR   |
| 15 | Richard Lionheart  | 1744—39-AA   |
| 16 | Shamanta Jones     | 2334—95-AC   |
| 17 | James Rooney       | 1401—34-TR   |
| 18 | Chandler Bing      | 3334—23-AA   |
| 19 | Jessica Burner     | 2277—99-RA   |
+----+--------------------+--------------+

表邮政编码

+------------+--------------+-------------+
| Postcode   | City         | Borough     |
+------------+--------------+-------------+
| 1100—99-AB | New York     | Manhattan   |
| 1201—34-CD | New York     | Manhattan   |
| 1401—34-TR | New York     | Bronx       |
| 1744—39-AA | New York     | Harlem      |
| 2334—95-AC | Newark       |             |
| 6334—95-AC | Detroit      | Greektown   |
| 3334—23-AA | Philadelphia | Penn Center |
| 2277—99-RA | Newark       |             |
+------------+--------------+-------------+

结果我想要

+--------------+--------------+-------------+---------------+
| City         | Total_City   | Borough     | Total_Borough |
+--------------+--------------+-------------+---------------+
| New York     | 7            | Manhattan   | 4             |
| New York     | 7            | Bronx       | 2             |
| New York     | 7            | Harlem      | 1             |
| Newark       | 2            |             | 2             |
| Philadelphia | 1            | Penn Center | 1             |
+--------------+--------------+-------------+---------------+

这就是我在查询中取得的成绩。这确实正确地计算了行政区/社区的数量，但不幸的是，它并没有显示城市总数。

SELECT City, Borough, COUNT(City) AS Total_City, COUNT(Borough) AS Total_Borough, 
    FROM `users` u 
    LEFT JOIN `postcodes` p ON p.postcode = u.postcode
GROUP BY City, Borough

参见我在http://rextester.com/DFRV4183上的例子

Answer 1

有一个办法..。

SELECT a.*, b.total total_city
  FROM 
     ( SELECT p.city
            , p.borough
            , COUNT(*) total 
         FROM postcodes p 
         JOIN myusers u 
           ON u.postcode = p.postcode 
        GROUP 
           BY city
            , borough
     ) a
  JOIN
     ( SELECT p.city
            , COUNT(*) total
         FROM postcodes p
         JOIN myusers u 
       ON u.postcode = p.postcode 
    GROUP 
           BY city
     ) b
    ON b.city = a.city;

你也可以这样做，但我不相信结果更容易理解.

SELECT city 
     , borough
     , COUNT(*) total 
  FROM postcodes p 
  JOIN myusers u 
    ON u.postcode = p.postcode 
 GROUP 
    BY city
     , borough WITH ROLLUP;

最后，您可以使用变量。也许我稍后会按这些思路发个答案..。如果有人不超过我。

Answer 2

SELECT b.City, Borough, Total_City, Total_Borough from
  (SELECT City, coalesce(COUNT(User),0) AS Total_City
    FROM `Users` u 
    right JOIN `Postcodes` p ON p.postcode = u.postcode
  GROUP BY City ) as c
inner join 
(
SELECT City, coalesce(Borough,'Undefined')as Borough,coalesce( COUNT(User),0) AS Total_Borough, p.postcode
    FROM `users` u 
    right JOIN `postcodes` p ON p.postcode = u.postcode
GROUP BY City, Borough
) as b 
on b.City=c.City