这是一些转储数据。

CREATE TABLE `customer` (
  `approve_datetime` datetime DEFAULT NULL,
  `created_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `customer` (`approve_datetime`, `created_date`)
VALUES
    ('2015-08-20 04:43:00','2015-08-20'),
    (NULL,'2015-09-03'),
    ('2015-09-17 02:17:00','2015-09-17'),
    (NULL,'2015-09-29'),
    ('2015-09-29 12:44:00','2015-09-29'),
    ('2015-10-08 03:09:00','2015-10-08'),
    ('2016-01-20 08:59:00','2016-01-19'),
    ('2016-05-03 09:38:00','2016-05-02'),
    ('2016-07-15 11:06:00','2016-07-15'),
    (NULL,'2016-08-30'),
    ('2016-10-18 12:55:00','2016-10-18'),
    (NULL,'2017-01-08'),
    (NULL,'2017-02-02'),
    ('2017-02-13 02:58:00','2017-02-13');


这是我当前的查询,无法正确处理30天的分组。

SELECT a.*
FROM customer a
WHERE a.approve_datetime IN (
        SELECT MIN(b.approve_datetime)
        FROM customer b
        WHERE b.created_date BETWEEN a.created_date
            AND DATE_ADD(a.created_date, INTERVAL 30 DAY)
    )


这给了我以下几点。

+---------------------+--------------+
| approve_datetime    | created_date |
+---------------------+--------------+
| 2015-08-20 04:43:00 | 2015-08-20   |
| 2015-09-17 02:17:00 | 2015-09-17   |
| 2015-09-29 12:44:00 | 2015-09-29   |
| 2015-10-08 03:09:00 | 2015-10-08   |
| 2016-01-20 08:59:00 | 2016-01-19   |
| 2016-05-03 09:38:00 | 2016-05-02   |
| 2016-07-15 11:06:00 | 2016-07-15   |
| 2016-10-18 12:55:00 | 2016-10-18   |
| 2017-02-13 02:58:00 | 2017-02-13   |
+---------------------+--------------+


可以更改查询以获得以下结果吗?

+---------------------+--------------+
| approve_datetime    | created_date |
+---------------------+--------------+
| 2015-08-20 04:43:00 | 2015-08-20   |
| 2015-09-29 12:44:00 | 2015-09-29   |
| 2016-01-20 08:59:00 | 2016-01-19   |
| 2016-05-03 09:38:00 | 2016-05-02   |
| 2016-07-15 11:06:00 | 2016-07-15   |
| 2016-10-18 12:55:00 | 2016-10-18   |
| 2017-02-13 02:58:00 | 2017-02-13   |
+---------------------+--------------+


请注意,具有created_date的2015-09-17和2015-10-08的记录已被删除,因为它们位于前一个记录(该特定组的最短日期)的30天内。 2015-08-20 + 30天从第一组开始,而2015-08-20是该组的最低日期。

我希望我要实现的目标是有意义的。

最佳答案

看看这个。结果不同,但看是否正确。第3列和第4列仅用于了解其工作原理。

SELECT
      min(b.approve_datetime) AS approve_datetime
    , min(b.created_date) AS created_date
    , DATEDIFF(b.created_date,(SELECT min(created_date) FROM customer)) / 30 AS dayd30
    , FLOOR( DATEDIFF(b.created_date,(SELECT min(created_date) FROM customer)) / 30 ) AS dayd30floorint
    FROM customer b
    GROUP BY FLOOR( DATEDIFF(b.created_date,(SELECT min(created_date) FROM customer)) / 30 )
    ORDER BY b.created_date ;


样品

MariaDB [testdb]> SELECT
    ->       min(b.approve_datetime) AS approve_datetime
    ->     , min(b.created_date) AS created_date
    ->     , DATEDIFF(b.created_date,(SELECT min(created_date) FROM customer)) / 30 AS dayd30
    ->     , FLOOR( DATEDIFF(b.created_date,(SELECT min(created_date) FROM customer)) / 30 ) AS dayd30floorint
    ->     FROM customer b
    ->     GROUP BY FLOOR( DATEDIFF(b.created_date,(SELECT min(created_date) FROM customer)) / 30 )
    ->     ORDER BY b.created_date ;
+---------------------+--------------+---------+----------------+
| approve_datetime    | created_date | dayd30  | dayd30floorint |
+---------------------+--------------+---------+----------------+
| 2015-08-20 04:43:00 | 2015-08-20   |  0.0000 |              0 |
| 2015-09-29 12:44:00 | 2015-09-29   |  1.3333 |              1 |
| 2016-01-20 08:59:00 | 2016-01-19   |  5.0667 |              5 |
| 2016-05-03 09:38:00 | 2016-05-02   |  8.5333 |              8 |
| 2016-07-15 11:06:00 | 2016-07-15   | 11.0000 |             11 |
| NULL                | 2016-08-30   | 12.5333 |             12 |
| 2016-10-18 12:55:00 | 2016-10-18   | 14.1667 |             14 |
| NULL                | 2017-01-08   | 16.9000 |             16 |
| NULL                | 2017-02-02   | 17.7333 |             17 |
| 2017-02-13 02:58:00 | 2017-02-13   | 18.1000 |             18 |
+---------------------+--------------+---------+----------------+
10 rows in set (0.00 sec)

MariaDB [testdb]>

关于mysql - MySQL查询以30天为间隔选择最小日期时间,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/42331773/

10-16 07:55