查询最近7天数据

1.查询最近7天订单金额
用到了虚表和UNION ALL的知识
其中
price是要查询的数据,orders是表名
IFNULL(b.price,0)是指b.price如果值为空，则将空值设置为0

SELECT a.click_date,IFNULL(b.price,0) AS price
FROM (
    SELECT CURDATE() AS click_date
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS click_date
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS click_date
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS click_date
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) AS click_date
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS click_date
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS click_date
) a LEFT JOIN (
  SELECT DATE AS DATETIME, SUM(price) AS price
  FROM orders
  GROUP BY DATE
) b ON a.click_date = b.datetime;

2.查询最近7天用户数量

SELECT a.click_date,IFNULL(b.username,0) AS username
FROM (
    SELECT CURDATE() AS click_date
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS click_date
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS click_date
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS click_date
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) AS click_date
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS click_date
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS click_date
) a LEFT JOIN (
  SELECT DATE AS DATETIME, COUNT(username) AS username
  FROM orders
  GROUP BY DATE
) b ON a.click_date = b.datetime;

对比发现只需要修改第一行和倒数第四行

SELECT a.click_date,IFNULL(b.price,0) AS price
SELECT DATE AS DATETIME, SUM(price) AS price

 注意这里的as price必须和上面的b.price的price对应

SELECT a.click_date,IFNULL(b.username,0) AS username
SELECT DATE AS DATETIME, COUNT(username) AS username