英文:
SQL selecting last month values if none are used still select them
问题
我正在选择上个月`订单`中最常用的付款方式，并将最常用的付款方式筛选到付款页面的顶部列表中。我现在面临的唯一问题是，当一个付款方式在一个整月内都没有被使用时，它会被跳过，不会显示出来。
我的问题是，是否有人有这种问题的一个好的例子，因为我在stackoverflow上找不到一个好的例子。
英文:
SELECT COUNT(wo.id), wm.*
FROM webshop_orders as wo
LEFT JOIN webshop_merchants as wm ON wo.merchant_id = wm.id
LEFT JOIN webshop_merchant_order mo ON mo.merchant_id = wm.id AND mo.language_id = 1
WHERE (mo.hidden = 0) AND wm.status = 1
AND DATE(wo.created_at) > CURDATE() - INTERVAL 1 MONTH
GROUP BY wo.merchant_id
ORDER BY COUNT(wo.id) DESC
I am selecting last month orders most used payment method and filtering the most used ones at the top of the list on the payment method page. The only problem i am now facing is that when a payment method doesn't get used for example an entire month it will skip the payment method and not show it.
My question is does somebody have a good example of this kind of problem because i cant find a good one on stackoverflow.
答案1
得分: 1
我对我的查询进行了一些更改，现在它可以正常工作。
SELECT wm.*,
（SELECT COUNT(wo.id)作为order_count FROM webshop_orders AS wo WHERE wo.merchant_id = wm.id AND DATE(wo.created_at) > CURDATE() - INTERVAL 1 MONTH AND wo.language_id =％d）作为order_count
FROM webshop_merchants AS wm
LEFT JOIN webshop_merchant_order mo ON mo.merchant_id = wm.id AND mo.language_id =％d
WHERE
mo.hidden = 0
AND wm.status = 1
ORDER BY order_count DESC
英文:
I have made some changes to my query and it works know.
SELECT wm.*,
(SELECT COUNT(wo.id) as order_count FROM webshop_orders AS wo WHERE wo.merchant_id = wm.id AND DATE(wo.created_at) > CURDATE() - INTERVAL 1 MONTH AND wo.language_id = %d) as order_count
FROM webshop_merchants AS wm
LEFT JOIN webshop_merchant_order mo ON mo.merchant_id = wm.id AND mo.language_id = %d
WHERE
mo.hidden = 0
AND wm.status = 1
ORDER BY order_count DESC
答案2
得分: 0
你尝试过将第一个WHERE语句((mo.hidden = 0))更改为：
(mo.hidden = 0 OR mo.hidden IS NULL) 吗？
英文:
Have you tried changing the first WHERE statement ((mo.hidden = 0))with:
(mo.hidden = 0 OR mo.hidden IS NULL)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论