mysql - 在一个查询中使用两个 COUNT 语句时出现未知列错误

Question

我尝试在一个 SQL 查询中运行 2 个不同的 COUNT，但收到此错误:

Column not found: 1054 Unknown column 'po.product_id' in 'on clause'

这是sql:

SELECT COUNT(*) vouchersleft, `prod_name`,
        `start_date`, 
        `end_date`,
        `reg_price`,
        `sale_price`,
        bbp.`prod_id`,
        `vouch_limits`,
        `sp_name`,
        `order_id`,
        `img_name`,
        `outlets`
        FROM `vouchers` as `v`
        INNER JOIN `bb_products` as `bbp` ON bbp.`prod_id` = v.`product_id`
        INNER JOIN `service_providers` as `sp` ON sp.`service_provider_id` = bbp.`provider_id`
        INNER JOIN `prod_pics` as `pp` ON pp.`prod_id` = bbp.`prod_id`
        INNER JOIN (SELECT COUNT(DISTINCT `outlet_id`) AS `outlets` FROM `prod_outlets` GROUP BY `product_id`) as `po` ON po.`product_id` = bbp.`prod_id`
        WHERE `frontend` = :frontend
        AND bbp.`featured` = :featured
        AND `order_id` = :order_id
        AND pp.`featured` = :featured_img
        GROUP BY bbp.`prod_id`

Answer 1

如果您想按列连接，则必须由 SELECT 语句返回该列。在您选择的列列表中包含 product_id。

更改:

INNER JOIN (SELECT COUNT(DISTINCT `outlet_id`) AS `outlets` FROM `prod_outlets` GROUP BY `product_id`) as `po` ON po.`product_id` = bbp.`prod_id`

致:

INNER JOIN (SELECT `product_id`, COUNT(DISTINCT `outlet_id`) AS `outlets` FROM `prod_outlets` GROUP BY `product_id`) as `po` ON po.`product_id` = bbp.`prod_id`

如果没有选择列，product_id 在此中间表(此处名为 po)之外不可见。这就是为什么您会收到错误未知列，因为唯一已知的列是导出。