写mysql，sql大佬看过来*-*

Question

表

 id entry  quit    date
 1     2       3   2019-07-08
 2     0      3    2019-07-10
 3     3      4   2019-07-12

entry代表入职，quit代表离职
我想得到

entry  quit         date
2       3         2019-07-08
0       0         2019-07-09
0       3         2019-07-10
0       0         2019-07-11
3       4         2019-07-12

7月9号没有就写0，然后查询是根据日期区间查的

Answer 1

SELECT
date,
MAX( sum ) AS sum
FROM
(
SELECT
@cdate := DATE_ADD( @cdate, INTERVAL - 1 DAY ) date,
0 AS sum
FROM
( SELECT @cdate := DATE_ADD( CURDATE( ), INTERVAL + 1 DAY ) FROM shopping_hibitRecord ) t1
WHERE
@cdate > ‘2017-08-03‘ UNION ALL
SELECT
DATE_FORMAT( ADDTIME, ‘%Y-%m-%d‘ ) AS date,
COUNT( * ) AS ‘sum‘
FROM
shopping_hibitRecord
WHERE
shopping_hibitRecord.ADDTIME >= ‘2017-08-03‘
AND deleteStatus = FALSE
AND TYPE = 0
GROUP BY
DATE DESC
) _tmpAllTable
GROUP BY
date DESC
找了一个不需要创建储存方法的方法