时间戳

首页 > 解决方案 > 如何计算加入表mysql

mysql - 如何计算加入表mysql

问题描述

我有 4 个表加入另一个是通过市场加入的，我需要帮助从每个表中生成总记录，表如下......

councils
------------------------
| council_id  | name   |
------------------------
|    1        |   c1   |
+-------------+--------+
|    1        |   c2   |
------------------------


Markets
----------------------------------
| market_id | council_id | name  |
+-----------+------------+-------+
|   1       |    1       | wells |
+-----------+------------+-------+
|   2       |    1       | John  |
+-----------+------------+-------+
|   3       |    2       | abcd  |
----------------------------------

Marketeers
-------------------------------------
| marketeer_id | market_id  | name  |
+--------------+------------+-------+
|   1          |    2       | a     |
+--------------+------------+-------+
|   2          |    3       | b     |
+--------------+------------+-------+
|   3          |    1       | c     |
-------------------------------------

Stations
----------------------------------
| station_id | council_id | name  |
+------------+------------+-------+
|   1        |    1       | x     |
+------------+------------+-------+
|   2        |    2       | y     |
+------------+------------+-------+
|   3        |    2       | z     |
-----------------------------------

SELECT 
    c.council_name, 
    COUNT(DISTINCT market_id) AS num_markets, 
    COUNT(DISTINCT station_id) AS num_stations, 
    COALESCE(t2.cnt, 0) AS num_markteers
FROM markets m
JOIN councils c ON m.council_id = c.council_id
LEFT JOIN stations s ON s.council_id = c.council_id
LEFT JOIN ( 
    SELECT m2.council_id, COUNT(DISTINCT marketeer_id) AS cnt
    FROM marketeers p 
    JOIN markets m2  ON m2.market_id= p.market_id
    GROUP BY m2.council_id
)t2 USING (c.council_id)
GROUP BY c.council_id;

错误：查询中的错误 (1064)：第 10 行的“.council_id) GROUP BY c.council_id”附近的语法错误

预期结果

-------------------------------------------------------------
| council_name | num_markets | num_stations | num_markteers |
-------------------------------------------------------------
|    c1        |   2         |    1         |    2          |
-------------------------------------------------------------
|    c2        |   1         |    2         |    1          |
-------------------------------------------------------------

标签： mysqlsqljoin

解决方案

你可以试试下面的 -

SELECT c.council_name, 
       COUNT(DISTINCT market_id) AS num_markets, 
       COUNT(DISTINCT station_id) AS num_stations, 
       COUNT(DISTINCT marketeer_id) AS num_markteers
FROM councils c 
join markets m ON m.council_id = c.council_id
left join Marketeers m2 m2.market_id= m.market_id
LEFT JOIN stations s ON s.council_id = c.council_id
GROUP BY m2.council_id,c.council_name

推荐阅读