即使为空也显示值 [英] Show values even if empty

问题描述

我正在使用以下内容显示过去7天添加的产品数量...即使COUNT = 0，我是否可以以某种方式定制查询以显示过去7天的所有商品?

I am using the following to show a count of products added over the last 7 days...Can i somehow tailor the query to show all the last 7 days even if COUNT=0?

按现状查询:

SELECT DAYNAME(dateadded) DAY, COUNT(*) COUNT 
FROM `products` 
WHERE (`dateadded` BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE() && site_url = 'mysite.com') 
GROUP BY DAY(dateadded)

推荐答案

添加一个带有日期的表(日期查找表)，然后:

Add a table with dates in it (a dates lookup table), then:

SELECT DAYNAME(d.FullDate) DAY, COUNT(*) COUNT
FROM dates d
LEFT OUTER JOIN products p ON d.FullDate = DATE(p.dateadded) 
    AND p.site_url = 'mysite.com'
WHERE d.FullDate BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE()    
GROUP BY d.FullDate

是的，它需要一点存储空间，但是它将使这样的查询容易得多.

It takes a little bit of storage, yes, but it will make queries like this a lot easier.

或者，您可以创建一个存储过程，该存储过程在7天之前和今天之间的日期之间循环，并为每个日期返回一行.

Alternatively, you can make a stored procedure that loops through dates between 7 days ago and today and returns one row for each.

这篇关于即使为空也显示值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！