2计数查询乘以结果

3pmvbmvn 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(299)

我正在尝试跨3个连接执行两个count语句。第一次计数显示的是正确的数字，但是第二次计数似乎出于某种原因将计数相乘？我检查了标记为复制的链接，但该示例中没有任何连接。

SELECT
    COUNT(DISTINCT `outlet_id`) AS `outlets`,
    `prod_name`,
    COUNT(`purchased`) AS `vouchersleft`
FROM
    `prod_outlets` AS `po`
INNER JOIN `bb_products` AS `bbp` ON po.`product_id` = bbp.`prod_id`
INNER JOIN `vouchers` AS `v` ON v.`product_id` = bbp.`prod_id`
GROUP BY
    bbp.`prod_id`;

它应该显示3个分支机构和5个凭证。但它输出了3个分支机构和15张凭证。因此，第二个计数乘以第一个，即：3x5=15

sql mysql php

来源：https://stackoverflow.com/questions/50631459/2-count-queries-multiplying-results

2条答案

按热度按时间

nue99wik1#

从描述中我了解到你得到的是叉积，这就是为什么你得到的vouchersleft的数字是错误的，我建议你在sun子句中计算你的count，然后把这个子句和你的主查询连接起来，比如

SELECT
    COUNT(DISTINCT `outlet_id`) AS `outlets`,
    `prod_name`,
    v.vouchersleft
FROM
    `prod_outlets` AS `po`
INNER JOIN `bb_products` AS `bbp` ON po.`product_id` = bbp.`prod_id`
INNER JOIN (
    SELECT product_id, COUNT(*) vouchersleft
    FROM vouchers
    GROUP BY product_id
) AS `v` ON v.`product_id` = bbp.`prod_id`
GROUP BY
    bbp.`prod_id`;

赞(0）回复(0）举报 2021-06-20

cotxawn72#

"SELECT COUNT(DISTINCT `outlet_id`) as `outlets`,
 `prod_name`,
 COUNT(distinct `purchased`) as `vouchersleft`
 FROM `prod_outlets` as `po`
 INNER JOIN `bb_products` as `bbp`
 ON po.`product_id` = bbp.`prod_id`
 INNER JOIN `vouchers` as `v`
 ON v.`product_id` = bbp.`prod_id`
 GROUP BY bbp.`prod_id`";

赞(0）回复(0）举报 2021-06-20

我来回答

2计数查询乘以结果

2条答案

相关问题

热门标签

最新问答