Query for valid records between two days and count by each day of latest month

Question

The table ShopOrder's columns include:

id   shopid starttime endtime 
1    123    2018-04-27  2018-04-28
2    234    2018-04-23  2018-04-30
3    189    2018-05-01  2018-05-30
4    321    2018-05-01  2018-05-29

I wan't to query for valid shop counts between two days and count by each day of latest month,the valid shop counts means the starttime<= $curDate <= endtime,and curDate is a variable of the each day of the leatest month.

Today is 2018-04-27，so the query result should be:

day          count
2018-04-27    2
2018-04-26    1
2018-04-25    1
2018-04-24    1
2018-04-23    1
2018-04-22    0
2018-04-21    0
……………………………………
2018-03-26    0

how can i achieve this use MySQL?

Answer 1

Converting between start and end dates and a range of individual dates is a challenge in SQL because you probably don't have a table which contains a row for each day in the current month to join on.

You can fill a table with dates using logic from one of the answers in How to populate a table with a range of dates?.

Or, since the range you want to produce is quite short, you can just create it manually in your query. It isn't completely clear what you mean by "the latest month" since your example ranges from 26 April to 27 March, but if the last 30 days is reasonable enough, you can use UNION to create this list.

Comparing date ranges discusses how to test whether a date is in a particular range or not, so putting the two things together gives you something like

SELECT
    DATE_SUB(DATE(NOW()), INTERVAL days_ago.days DAY) day,
    COUNT(ShopOrder.id) count
FROM
(SELECT 0 days 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 UNION
 SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION
 SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION
 SELECT 20 UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION
 SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29)
    AS days_ago
LEFT JOIN ShopOrder
    ON DATE_SUB(DATE(NOW()), INTERVAL days_ago.days DAY) <= ShopOrder.endtime
    AND DATE_SUB(DATE(NOW()), INTERVAL days_ago.days DAY) >= ShopOrder.starttime
GROUP BY days_ago.days;