oracle查询最近7天数据没有展示0

假设现在有两张表order和user

order表

order_no订单号	create_time创建时间	price价格
GC123	2017-3-17	1600
GC124	2017-3-22	120
GC125	2017-3-22	100

---

user表

user_id	order_no
1	GC123
1	GC124
1	GC125

---

现在需要统计用户“1”在最近7天中每天的订单金额以及数量，如果哪天没有的话，展示0，既是展示下图结果(假设今天是23号)

createTime	price	count
2017-03-17	1600	1
2017-03-18	0	0
2017-03-19	0	0
2017-03-20	0	0
2017-03-21	0	0
2017-03-22	220	2
2017-03-23	0	0

---

像这种比较复杂一点的sql，一下子写出完整的sql显然不太好写，需要一点点分析。首先，查询最近7天用户“1”的订单数据，这就话可以分成“最近7天”和“用户1的订单数据”。

---

sql查询最近7天数据(以年-月-日结果展示)

SELECT 
    to_char (SYSDATE- LEVEL + 1, 'yyyy-mm-dd') today 
  FROM
    DUAL connect BY LEVEL <= 7

查询结果

---

查询用户“1”的订单数据

SELECT 
  to_char(o.create_time, 'yyyy-mm-dd') createTime,
  nvl(SUM(o.price), 0) price,
  nvl(COUNT(1), 0) count 
FROM
  orders o,users u 
WHERE u.order_no = o.order_no 
  AND u.user_id = '1' 
GROUP BY to_char (o.create_time, 'yyyy-mm-dd')

查询结果

这句sql需要注意一下group by的使用，group by是结合聚合函数sum(和)、max(最大)、min(最小)、count(数量)、avg(平均)等使用的，然后select查询的字段必须包含在group by后或者聚合函数中作为分组依据，如果没有，sql就会报错。就比如以上sql中to_char(o.create_time, 'yyyy-mm-dd') 就必须出现在group by后作为分组依据，而聚合函数列则不需要

---

现在就是需要把两个sql关联起来即可，连接两个sql语句有left join、 inner join和right join，使用哪个才可以完成需要的查询呢？这里需要理解三者的却别
left join(左连接) 返回左表中所有记录和右表中连接字段相等的记录
right join(右连接) 和左连接相反
inner join(等值相连、内连接) 只返回两个表中连接字段相等的记录

现在就可以来连接两个sql语句了，以查询“最近7天”为“左表”，使用left join，可以这样写

SELECT 
  days.createTime createTime ,
  nvl (m.price, 0) price,
  nvl (m.COUNT, 0) count 
FROM
  (SELECT 
    to_char (SYSDATE- LEVEL + 1, 'yyyy-mm-dd') createTime  
  FROM
    DUAL connect BY LEVEL <= 7) days 
  LEFT JOIN 
    (SELECT 
      to_char (o.create_time, 'yyyy-mm-dd') createTime,
      nvl (SUM(o.price), 0) price,
      nvl (COUNT(1), 0) count 
    FROM
      orders o,users u 
    WHERE u.order_no = o.order_no 
      AND u.user_id = '1' 
    GROUP BY to_char (o.create_time, 'yyyy-mm-dd')) m 
    ON days.createTime  = m.createTime 
GROUP BY days.createTime ,m.price, m.count  
ORDER BY days.createTime   

查询结果