mysql近七天,在MYSQL中从Now（）选择最近7天

Thankyou for taking the time to look at my question.

I have this MYSQL query:

foreach( $wpdb->get_results(

"SELECT wp_pixelcart_calendar.datefield AS DATE,

IFNULL(SUM(wp_pixelcart_daily_sales.quantity),0) AS total_sales

FROM wp_pixelcart_daily_sales RIGHT JOIN wp_pixelcart_calendar ON (DATE(wp_pixelcart_daily_sales.order_date) = wp_pixelcart_calendar.datefield)

WHERE (wp_pixelcart_calendar.datefield BETWEEN (SELECT MIN(DATE(order_date)) FROM wp_pixelcart_daily_sales) AND (SELECT MAX(DATE(order_date)) FROM wp_pixelcart_daily_sales))

GROUP BY DATE"

) as $key => $row) {

echo "
". $row->DATE . "',". $row->total_sales . "],";

}

I'm having a hard time to display the last seven days from now in the query, ive been playing around with:

BETWEEN (SELECT MIN(DATE(order_date)) FROM wp_pixelcart_daily_sales) AND (SELECT MAX(DATE(order_date)) FROM wp_pixelcart_daily_sales))

To this:

BETWEEN NOW() FROM wp_pixelcart_daily_sales) AND DATE_ADD(NOW(), INTERVAL 7 DAY) FROM wp_pixelcart_daily_sales))

But this doesn't seem to work.

Any help appreciated.

Thanks

解决方案

if this is not working, returning 0 results, consider swapping the dates' range:

BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW()