查询一周内的统计数据sql

查询一周内的统计数据sql

```java
SELECT 
t.stime ,CASE WHEN ISNULL(SUM(u.buy_u)) THEN '0' ELSE SUM(u.buy_u) END  AS num 	
FROM (
	SELECT DATE_SUB(CURDATE(), INTERVAL 7 DAY) AS stime 
	UNION  SELECT DATE_SUB(CURDATE(), INTERVAL 8 DAY) AS stime
	UNION  SELECT DATE_SUB(CURDATE(), INTERVAL 9 DAY) AS stime
	UNION  SELECT DATE_SUB(CURDATE(), INTERVAL 10 DAY) AS stime
	UNION  SELECT DATE_SUB(CURDATE(), INTERVAL 11 DAY) AS stime
	UNION  SELECT DATE_SUB(CURDATE(), INTERVAL 12 DAY) AS stime
	UNION  SELECT DATE_SUB(CURDATE(), INTERVAL 13 DAY)AS stime
	)  t 
	LEFT JOIN user_income u 
	ON CAST(u.created_time AS DATE) <= t.stime
	WHERE u.is_direct_selling =1
	GROUP BY t.stime
	ORDER BY t.stime DESC

这里
`SELECT DATE_SUB(CURDATE(), INTERVAL 0 DAY) AS stime 
	UNION  SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS stime
	UNION  SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS stime
	UNION  SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS stime
	UNION  SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) AS stime
	UNION  SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS stime
	UNION  SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY)AS stime`
	是在查询当前一周的日期

在进行日期比较的时候，如果要获取当日的数据 需要

CAST(u.created_time AS DATE) <= t.stime

将字符串转化为date 这里转化之后不会计算HH:mm:ss部分的值 使得
2020-01-01 15:16:10 和2020-01-01 相等。