mysql - 在mysql中查找开始日期和结束日期之间的日期

Question

我有两列，一列是开始日期，另一列是结束日期。我需要找到两个日期之间的每一天。

需要在另一个表中有两个 coloums，一个名称 pno 指的是第一个表中的 pno 的 id，另一个是开始之间的日期日期和结束日期。

例如，这可能是我的输入

 pno         startdate        end datep1          2012-12-03      2012-12-06     p2          2013-01-05      2013-01-08p3          2013-01-15      2012-01-20

这必须是我的输出。

pno          dates----------p01          2012-12-03 p01          2012-12-04 p01          2012-12-05 p01          2012-12-06p02          2013-01-05p02          2013-01-06p02          2013-01-07p02          2013-01-08p03          2013-01-15...

Answer 1

你可以这样做

SELECT pno, startdate + INTERVAL q.n - 1 DAY dates
  FROM table1 t CROSS JOIN
(
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n
) q 
 WHERE q.n - 1 <= DATEDIFF(enddate, startdate) 
 ORDER BY pno, dates

子查询生成一个从 1 到 100 的数字序列。您可以根据需要调整它(如果日期差异超过或少于 100 天)，或者如果您执行很多这样的查询。

输出:

+------+------------+| pno  | dates      |+------+------------+| p1   | 2012-12-03 || p1   | 2012-12-04 || p1   | 2012-12-05 || p1   | 2012-12-06 || p2   | 2013-01-05 || p2   | 2013-01-06 || p2   | 2013-01-07 || p2   | 2013-01-08 || p3   | 2013-01-15 || p3   | 2013-01-16 || p3   | 2013-01-17 || p3   | 2013-01-18 || p3   | 2013-01-19 || p3   | 2013-01-20 |+------+------------+

Here is SQLFiddle demo

---

UPDATE: To create and populate persisted tally table use

CREATE TABLE tally (n INT NOT NULL PRIMARY KEY);

INSERT INTO tally (n)
SELECT a.n + b.n * 10 + c.n * 100 + d.n * 1000 + 1 n
 FROM 
(SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
,(SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
,(SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d
ORDER BY n;

计数表中的数字序列从 1 到 10000。这样您就可以处理跨越 27 年以上的日期范围。

现在查询归结为

SELECT pno, startdate + INTERVAL q.n - 1 DAY dates
  FROM table1 t CROSS JOIN tally q 
 WHERE q.n - 1 <= DATEDIFF(enddate, startdate) 
 ORDER BY pno, dates

这是 SQLFiddle 演示