2个计数查询乘以结果
2个计数查询乘以结果
提问于 2018-06-01 03:26:47
我试图在3个joins中执行两个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
回答 2
Stack Overflow用户
回答已采纳
发布于 2018-06-01 14:13:03
根据我所理解的描述，你得到的是叉积，这就是为什么你得到了错误的数字vouchersleft，我建议你在sun子句中计算你的计数，然后将这个子句与你的主查询连接起来，比如
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`;Stack Overflow用户
发布于 2018-06-01 03:27:36
"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`";页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50631459
复制相关文章
相似问题