MySQL根据另一列创建日期范围 [英] 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

我正在尝试生成一个报告，它将使 0 数量的值（ qty ）。我不能硬编码日期，它们需要由第二列的值决定。从上述情况看，报告应如下所示：

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

所有的 GROUP BY 和 UNION 查询给了我个人条目，但我无法弄清楚如何创建日期范围。

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

推荐答案

假设表名是日历，以下查询将执行以下工作：

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

在内部查询中，我们基本上将日历表连接到两个本身：第一个连接（ c code> prev table）为每个 c 表行选择最小日期小于日期。这给我们这个行所属的间隔开始。第二个连接选择间隔以相同的方式结束。

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

这篇关于MySQL根据另一列创建日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！