MySQL查询选择分钟日期时间30天的间隔
问题描述:
下面是一些转储数据分组..MySQL查询选择分钟日期时间30天的间隔
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年9月17日和2015年10月8日已被删除,因为它们中的前记录下哪些是特定群体的最低日起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]>
+0
你知道我认为这将为我的情况做@Bernd ..感谢一堆!很聪明.. – Rick
什么是你的主键? – Strawberry
有没有一个 – Rick
如果你没有一个主键,你真的没有一个表:-( – Strawberry