MySQL，按周查询，结果保留没有记录的周

Question

我想按周查询某表(存款记录)的一个属性(金额)的和，MySQL代码如下，但由于中间有些周没有记录，查询结果不会有这些周，我想像外连接一样保留这些周(对应金额即为null或0，随便)，请问如何实现？
我能想到的是建一个记录了很多连续周的表，再用外连接，但感觉有点冗余；而且感觉这类问题很常见；想问一下有没有直接一点的方法

SELECT DATE_FORMAT(DEPOSIT_TIME,'%x-%v') WEEK,SUM(AMOUNT) FROM deposit GROUP BY WEEK ORDER BY WEEK;

Answer 1

一年52周，列出来呗

select a.click_week,ifnull(b.count,0) as count
    from (
        SELECT 0 as click_week
        union all
        SELECT 1 as click_week
        union all
        SELECT 2 as click_week
        union all
        SELECT 3 as click_week
        union all
        SELECT 4 as click_week
        union all
        SELECT 5 as click_week
        union all
        SELECT 6 as click_week
        union all
        SELECT d as click_week
        ....
        union all
        SELECT 52 as click_week
    ) a left join (
      select  week(DEPOSIT_TIME) as click_week, count(*) as count
      from deposit
      group by  week(DEPOSIT_TIME)
    ) b on a.click_week = b.click_week;