mysql交叉连接统计在2016年10月之前的每一天入职的客户经理数

统计在2016年10月之前的每一天入职的客户经理数。
完整SQL

SELECT days.dt,COUNT(user_id) from t_manager m RIGHT OUTER JOIN 

(SELECT DATE_ADD('2016-01-01',INTERVAL (ones.num + tens.num + hundreds.num ) DAY) dt

FROM 
(SELECT 0 num UNION all
SELECT 1 num UNION all
SELECT 2 num UNION all
SELECT 3 num UNION all
SELECT 4 num UNION all
SELECT 5 num UNION all
SELECT 6 num UNION all
SELECT 7 num UNION all
SELECT 8 num UNION all
SELECT 9 num )ones
CROSS JOIN
(SELECT 0 num UNION all
SELECT 10 num UNION all
SELECT 20 num UNION all
SELECT 30 num UNION all
SELECT 40 num UNION all
SELECT 50 num UNION all
SELECT 60 num UNION all
SELECT 70 num UNION all
SELECT 80 num UNION all
SELECT 90 num)tens

CROSS JOIN
( SELECT 0 num union ALL
SELECT 100 num union ALL
SELECT 200 num union ALL
SELECT 300 num ) hundreds

WHERE DATE_ADD('2016-01-01',INTERVAL (ones.num + tens.num + hundreds.num) 

DAY)<'2016-10-01') days
on days.dt=m.entry_time

GROUP BY days.dt

ORDER BY 1;

1. 使用笛卡尔积构造365，以下执行共400条记录，然后使用ones.num + tens.num + hundreds.num 将结果集加起来就得到了0-399之间的数。

SELECT *
FROM 
(SELECT 0 num UNION all
SELECT 1 num UNION all
SELECT 2 num UNION all
SELECT 3 num UNION all
SELECT 4 num UNION all
SELECT 5 num UNION all
SELECT 6 num UNION all
SELECT 7 num UNION all
SELECT 8 num UNION all
SELECT 9 num )ones
CROSS JOIN
(SELECT 0 num UNION all
SELECT 10 num UNION all
SELECT 20 num UNION all
SELECT 30 num UNION all
SELECT 40 num UNION all
SELECT 50 num UNION all
SELECT 60 num UNION all
SELECT 70 num UNION all
SELECT 80 num UNION all
SELECT 90 num)tens

CROSS JOIN
( SELECT 0 num union ALL
SELECT 100 num union ALL
SELECT 200 num union ALL
SELECT 300 num ) hundreds

1. 将数字集转换为日期集，使用date_add()函数将结果集中的数字都加上2016-01-01，然后添加过滤条件排除2016-10-01之后的所有日期。

2. .查询2016年10月1日之前每一天入职的客户经理数。