Mysql构造临时维度表

-- V1:
select  t1.date
       ,t1.terrace_id
       ,ifnull(t1.cnt,0) as reg
       ,ifnull(t2.cnt,0) as ent
from 
(

select  date(register_date) as date
       ,terrace_id 
       ,count(1) as cnt
from ad_register
where register_date > '2022-01-01' -- and terrace_id =2
group by  date(register_date)
         ,terrace_id
) t1 
left join 

(
select  date(entry_date) as date
       ,terrace_id 
       ,count(1) as cnt
from ad_entry
where entry_date > '2022-01-01'  --  and terrace_id =2
group by  date(entry_date)
         ,terrace_id

) t2
on t1.date = t2.date and t1.terrace_id = t2.terrace_id
order by terrace_id,date asc;

解决方案：构建双维度

-- 单维度日期序列
SELECT DATE_ADD('2022-01-01', INTERVAL seq.seq DAY) AS date
FROM (
    SELECT (t4.num*1000 + t3.num*100 + t2.num*10 + t1.num) AS seq
    FROM
        (SELECT 0 AS num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t1,
        (SELECT 0 AS num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t2,
        (SELECT 0 AS num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t3,
        (SELECT 0 AS num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t4
) AS seq
WHERE DATE_ADD('2022-01-01', INTERVAL seq.seq DAY) < CURDATE()
ORDER BY date;	

-- 双维度
SELECT 
    dates.date,
    categories.category
FROM (
    SELECT DATE_ADD('2022-01-01', INTERVAL seq.seq DAY) AS date
    FROM (
        SELECT (t4.num*1000 + t3.num*100 + t2.num*10 + t1.num) AS seq
        FROM
            (SELECT 0 AS num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
             UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t1,
            (SELECT 0 AS num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
             UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t2,
            (SELECT 0 AS num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
             UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t3,
            (SELECT 0 AS num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
             UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t4
    ) AS seq
    WHERE DATE_ADD('2022-01-01', INTERVAL seq.seq DAY) < CURDATE()
) AS dates
CROSS JOIN (
    SELECT 1 AS category UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
) AS categories
ORDER BY dates.date, categories.category;

构造实战案例：

-- v2:
SELECT
    dim.date,
    dim.category as terrace_id,
    IFNULL(t1.cnt, 0) AS reg,
    IFNULL(t2.cnt, 0) AS ent
FROM
    (
        -- 双维度查询
        SELECT
            dates.date,
            categories.category
        FROM
            (
                SELECT
                    DATE_ADD('2022-01-01', INTERVAL seq.seq DAY) AS date
                FROM
                    (
                        -- 序列生成器
                        SELECT
                            (t4.num * 1000 + t3.num * 100 + t2.num * 10 + t1.num) AS seq
                        FROM
                            (
                                SELECT 0 AS num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
                            ) AS t1,
                            (
                                SELECT 0 AS num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
                            ) AS t2,
                            (
                                SELECT 0 AS num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
                            ) AS t3,
                            (
                                SELECT 0 AS num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
                            ) AS t4
                    ) AS seq
                WHERE
                    DATE_ADD('2022-01-01', INTERVAL seq.seq DAY) < CURDATE()
            ) AS dates
        CROSS JOIN
            (
                SELECT 1 AS category UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
            ) AS categories
        ORDER BY
            dates.date,
            categories.category
    ) AS dim
LEFT JOIN
    (
        SELECT
            DATE(register_date) AS date,
            terrace_id,
            COUNT(1) AS cnt
        FROM
            ad_register
        WHERE
            register_date > '2022-01-01'
        GROUP BY
            DATE(register_date),
            terrace_id
    ) AS t1 ON dim.date = t1.date
    AND dim.category = t1.terrace_id
LEFT JOIN
    (
        SELECT
            DATE(entry_date) AS date,
            terrace_id,
            COUNT(1) AS cnt
        FROM
            ad_entry
        WHERE
            entry_date > '2022-01-01'
        GROUP BY
            DATE(entry_date),
            terrace_id
    ) AS t2 ON dim.date = t2.date
    AND dim.category = t2.terrace_id
order by date asc ;