我正在尝试计算在我的数据库中找到的品牌总数

我有3张 table

车辆品牌

+----+------+
| id | make |
+----+------+
|  1 | Audi |
|  2 | BMW  |
+----+------+

车辆广告

+----+-----------+----------+------+-----------+
| id | make_code |  model   | year | dealer_id |
+----+-----------+----------+------+-----------+
|  1 |         2 | 5 series | 2010 |        0  |
+----+-----------+----------+------+-----------+
|  2 |         2 | 3 series | 2014 |        20 |
+----+-----------+----------+------+-----------+

经销商

+----+------------+------+
| id | dealername | make |
+----+------------+------+
| 20 | bla bla    |    2 |
+----+------------+------+

这是我的 sql 查询，我尝试将其 modify this所以我可以按品牌和 dealer_id 分组

SELECT j.make AS make, j.id AS id, sum(j.count) AS count FROM 
(
    (
        SELECT v.make, sum(count+dealerCount)
        FROM 
             ( SELECT a.dealer_id, v.make AS make, a.make_code, COUNT(*) AS count
                    FROM `vehicle_make` AS v
                    JOIN `vehicle_ads` AS a
                    ON (v.id=a.make_code)
                    WHERE a.dealer_id>0
                    GROUP BY a.dealer_id, a.make_code
              ) AS g
            JOIN
              ( SELECT a.dealer_id, v.make, a.make_code, COUNT(*) AS dealerCount
                    FROM `vehicle_make` AS v
                    JOIN `vehicle_ads` AS a
                    ON (v.id=a.make_code)
                    WHERE a.dealer_id>0
                    GROUP BY a.dealer_id
              ) AS gl
              ON gl.dealer_id = g.dealer_id
            JOIN
              ( SELECT a.make_code, COUNT(*) AS makeCount
                    FROM `vehicle_make` AS v
                    JOIN `vehicle_ads` AS a
                    ON (v.id=a.make_code)
                    WHERE a.dealer_id>0
                    GROUP BY a.make_code
              ) AS gg
            ON gg.make_code=g.make_code 
    )
    UNION
    (
        SELECT v.make AS make, v.id AS id, COUNT(*) AS count
                    FROM `dealership` AS d
                    JOIN `vehicle_make` AS v
                    ON (v.id=d.make)
                    GROUP BY d.make
    )
) AS j GROUP BY j.make

现在我收到这个错误。

1052 - 字段列表中的“make”列不明确

输出应该是这样的

+--------+----+-------+
|  make  | id | count |
+--------+----+-------+
| Audi   |  1 |   300 |
| BMW    |  2 |   150 |
| Toyota |  3 |    50 |
+--------+----+-------+

基本上它应该在 dealership 表中获取品牌总数，并在 vechile_ads 表中获取品牌总数(按品牌和 dealer_id 分组)

知道我在这里做错了什么。

更新: SQL FIDDLE

在上面的 fiddle 中，BMW 的 NUMADS 应该是 1(按 dealer_id 分组)并且返回的列应该是 make , make_code, count 其中 count 是 NUMADS + NUMDEALER

最佳答案

试试这个查询:

SELECT make.id,
       make.make,
       ( IFNULL(ads.cout, 0) + IFNULL(deal.cout, 0) ) AS cout
FROM   vehicle_make make
       LEFT OUTER JOIN (SELECT make_code                 AS id,
                               Count(DISTINCT dealer_id) AS cout
                        FROM   vehicle_ads
                        GROUP  BY make_code) ads
                    ON make.id = ads.id
       LEFT OUTER JOIN (SELECT make     AS id,
                               Count(*) AS cout
                        FROM   dealership
                        WHERE  make IS NOT NULL
                        GROUP  BY make) deal
                    ON make.id = deal.id; 

子查询 ads 将为您提供 distinct dealers 的 count，他们为来自 的 make 发布了广告>vehicle_ads 表。子查询 deal 将为您提供 dealership 表中的制造数量。最后，使用 left outer join 将 vehicle_make 表连接到 ads 和 deal 以便结果应包含所有 id 和 make 并添加来自两个子查询的计数以获得所需的结果。

Sample SqlFiddle

注意:在您的 sql fiddle 中，将 count(*) 替换为 count(distinct dealer_id) 将为您提供所需的内容。检查here .

关于Mysql按2个字段分组，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/26955453/