gpt4 book ai didi

mysql - COUNT 组的 mysql 总数

转载 作者:行者123 更新时间:2023-11-29 19:42:37 28 4
gpt4 key购买 nike

这个 mysql 查询给出了我的产品的销售数量(total 和total_staff),按一天中的天数和小时数分组。我想要每个产品的 totaltotal_staff 的总和(不按任何内容分组,除了按 id 分组)。我可以在同一个查询中执行此操作吗?

SELECT p.name, p.color, DATE(date_create) as jour,HOUR(date_create) AS h,
SUM(CASE WHEN s.staff=0 THEN 1 ELSE 0 END) as total,
SUM(CASE WHEN s.staff=1 THEN 1 ELSE 0 END) as total_staff
FROM manifsSubCategories msc
LEFT JOIN products_subCategories psc ON msc.id=psc.manifSubCategory_id
LEFT JOIN sales s ON psc.product_id=s.product_id
LEFT JOIN products p ON psc.product_id=p.id
LEFT JOIN manifsCategories ON manifCategory_id=manifsCategories.id
WHERE manifCategory_id=1 AND s.category_id=1 GROUP BY jour,h ORDER BY p.id DESC

最佳答案

只需省略其他列即可:

SELECT p.name,
SUM(CASE WHEN s.staff=0 THEN 1 ELSE 0 END) as total,
SUM(CASE WHEN s.staff=1 THEN 1 ELSE 0 END) as total_staff
FROM manifsSubCategories msc
LEFT JOIN products_subCategories psc ON msc.id=psc.manifSubCategory_id
LEFT JOIN sales s ON psc.product_id=s.product_id
LEFT JOIN products p ON psc.product_id=p.id
LEFT JOIN manifsCategories ON manifCategory_id=manifsCategories.id
WHERE manifCategory_id=1 AND s.category_id=1
GROUP BY p.name
ORDER BY p.id DESC

顺便说一句 - 您的 WHERE 子句是错误的。您正在按表过滤 LEFT JOIN ,由于 NULL 比较,这会将您的联接变成内联接
左连接右表上的条件只能放置在 ON 子句内。

关于mysql - COUNT 组的 mysql 总数,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/41257945/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com