MySQL creating date ranges based on another column

问题:

I have a calendar table that looks like this:

    date   |  qty
-------------------
1998-11-18 |  2
1998-11-19 |  0
1998-11-20 |  0
1998-11-21 |  0
1998-11-22 |  3
1998-11-23 |  0
1998-11-24 |  0
1998-11-25 |  3

I am trying to generate a report which would give the ranges of the entries with the 0 values for quantity (qty). I can't hard code the dates, they need to be determined by the value of the second column. From the above, the report should look like this:

1998-11-19 - 1998-11-21  
1998-11-23 - 1998-11-24

All the GROUP BY and UNION queries give me the individual entries but I can't figure out how to create the date ranges.

回答1:

Assuming the table name is calendar, the following query does the job:

SELECT 
   MIN(t.date),
   MAX(t.date)
from
    (SELECT
        c.date,
        MAX(prev.date) mindat,
        MIN(next.date) maxdat
    FROM 
        calendar c,
        calendar prev,
        calendar next
    WHERE 
        c.qty = 0 AND
        c.date > prev.date AND prev.qty != 0 AND
        c.date < next.date AND next.qty != 0
    GROUP BY
        c.date) t
GROUP BY
    mindat, maxdat

In internal query we basically join calendar table to itself twice: first join (c table to prev table) selects maximum date which is smaller than date in for each c table row. That gives us the beginning of interval the row belongs to. Second join selects interval ending the same way.

External query extracts minimum and maximum dates from the intervals.

I tested query with your data and here is the output:

min(t.date) max(t.date)
1998-11-19  1998-11-21
1998-11-23  1998-11-24

回答2:

After trying a number of things, the query that solves the problem is:

SELECT start_date, MAX(dates) AS End
FROM (
  SELECT calendar.*,
         @f:=CONVERT(
             IF(@r<=>gty AND DATEDIFF(dates, @d)=1, @f, dates), DATE
           ) AS start_date,
           @d:=dates, @r:=gty
  FROM     calendar JOIN (SELECT @d:=NULL) AS init
  where gty="0"
  ORDER BY gty, dates
)  AS t
GROUP BY start_date, gty;

Thank you all for your help.