MySQL 将空 WEEKS 数据包含为 0

Question

有没有办法从空结果中包含空周值？或者我如何才能联合空缺的几周

我有一些疑问

SELECT 
    o.user_id , WEEK(FROM_UNIXTIME(o.cdate, '%Y-%m-%d'),7) as week_number,
FROM 
    (_orders AS `o`) 
WHERE 
    o.cdate BETWEEN '1505409460' AND '1540815218'
GROUP BY 
    week_number

结果

1
2
4
6
8

请求的结果

1
2
3
4
5
6
7
8

Answer 1

这只是一个例子，有很多方法可以实现这一点。第一步是拥有或生成一组整数。实际上，拥有一张这样的表格非常方便。这里我使用 2 个子查询交叉连接来生成 100 行(n = 0 到 99)

select
   ns.n, sq.*
from (
        select
           d1.digit + (d10.digit*10) as n
        from (
              SELECT 0 AS digit UNION ALL
              SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL  
              SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL 
              SELECT 9
              ) d1
        cross join (
              SELECT 0 AS digit UNION ALL
              SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL  
              SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL 
              SELECT 9
            ) d10
    ) ns
left join (

    your query goes here

    ) sq on ns.n = sq.week_number
where n between 1 and 52
order by n