mysql查询中sql不在语法之间

ru9i0ody  于 2021-08-09  发布在  Java
关注(0)|答案(3)|浏览(355)

在table上 calendar_recovery_interval_hour_2020 我把每一天分成一个小时的时段。 2020-04-14 ```
+---------------------+---------------------+-----+
| start_date | end_date | sID |
+---------------------+---------------------+-----+
| 2020-04-14 00:00:00 | 2020-04-14 00:59:00 | 1 |
| 2020-04-14 01:00:00 | 2020-04-14 01:59:00 | 2 |
| 2020-04-14 02:00:00 | 2020-04-14 02:59:00 | 3 |
| 2020-04-14 03:00:00 | 2020-04-14 03:59:00 | 4 |
| 2020-04-14 04:00:00 | 2020-04-14 04:59:00 | 5 |
| 2020-04-14 05:00:00 | 2020-04-14 05:59:00 | 6 |
| 2020-04-14 06:00:00 | 2020-04-14 06:59:00 | 7 |
| 2020-04-14 07:00:00 | 2020-04-14 07:59:00 | 8 |
| 2020-04-14 08:00:00 | 2020-04-14 08:59:00 | 9 |
| 2020-04-14 09:00:00 | 2020-04-14 09:59:00 | 10 |
| 2020-04-14 10:00:00 | 2020-04-14 10:59:00 | 11 |
| 2020-04-14 11:00:00 | 2020-04-14 11:59:00 | 12 |
| 2020-04-14 12:00:00 | 2020-04-14 12:59:00 | 13 |
| 2020-04-14 13:00:00 | 2020-04-14 13:59:00 | 14 |
| 2020-04-14 14:00:00 | 2020-04-14 14:59:00 | 15 |
| 2020-04-14 15:00:00 | 2020-04-14 15:59:00 | 16 |
| 2020-04-14 16:00:00 | 2020-04-14 16:59:00 | 17 |
| 2020-04-14 17:00:00 | 2020-04-14 17:59:00 | 18 |
| 2020-04-14 18:00:00 | 2020-04-14 18:59:00 | 19 |
| 2020-04-14 19:00:00 | 2020-04-14 19:59:00 | 20 |
| 2020-04-14 20:00:00 | 2020-04-14 20:59:00 | 21 |
| 2020-04-14 21:00:00 | 2020-04-14 21:59:00 | 22 |
| 2020-04-14 22:00:00 | 2020-04-14 22:59:00 | 23 |
| 2020-04-14 23:00:00 | 2020-04-14 23:59:00 | 24 |
+---------------------+---------------------+-----+
24 rows in set

现在我要在第二张table上找 `stable_2020` 对于表中缺少的时隙 `calendar_recovery_interval_hour_2020` ```
+------+---------------------+-----+
| STUX | sdatetime           | sID |
+------+---------------------+-----+
|   14 | 2020-04-14 01:09:00 |   1 |
|   14 | 2020-04-14 01:59:00 |   2 |
|   14 | 2020-04-14 02:02:00 |   3 |
|   14 | 2020-04-14 02:52:00 |   4 |
+------+---------------------+-----+
4 rows in set

这是教程
在这个例子中，我需要下面的输出，因为 stable_2020 我有四排
时隙两行

| 2020-04-14 01:00:00 | 2020-04-14 01:59:00 |   2 |

时隙上的另外两排

| 2020-04-14 02:00:00 | 2020-04-14 02:59:00 |   3 |

所需输出

+---------------------+---------------------+
| start_date          | end_date            |
+---------------------+---------------------+
| 2020-04-14 00:00:00 | 2020-04-14 00:59:00 |
| 2020-04-14 03:00:00 | 2020-04-14 03:59:00 |
| 2020-04-14 04:00:00 | 2020-04-14 04:59:00 |
| 2020-04-14 05:00:00 | 2020-04-14 05:59:00 |
| 2020-04-14 06:00:00 | 2020-04-14 06:59:00 |
| 2020-04-14 07:00:00 | 2020-04-14 07:59:00 |
| 2020-04-14 08:00:00 | 2020-04-14 08:59:00 |
| 2020-04-14 09:00:00 | 2020-04-14 09:59:00 |
| 2020-04-14 10:00:00 | 2020-04-14 10:59:00 |
| 2020-04-14 11:00:00 | 2020-04-14 11:59:00 |
| 2020-04-14 12:00:00 | 2020-04-14 12:59:00 |
| 2020-04-14 13:00:00 | 2020-04-14 13:59:00 |
| 2020-04-14 14:00:00 | 2020-04-14 14:59:00 |
| 2020-04-14 15:00:00 | 2020-04-14 15:59:00 |
| 2020-04-14 16:00:00 | 2020-04-14 16:59:00 |
| 2020-04-14 17:00:00 | 2020-04-14 17:59:00 |
| 2020-04-14 18:00:00 | 2020-04-14 18:59:00 |
| 2020-04-14 19:00:00 | 2020-04-14 19:59:00 |
| 2020-04-14 20:00:00 | 2020-04-14 20:59:00 |
| 2020-04-14 21:00:00 | 2020-04-14 21:59:00 |
| 2020-04-14 22:00:00 | 2020-04-14 22:59:00 |
| 2020-04-14 23:00:00 | 2020-04-14 23:59:00 |
+---------------------+---------------------+

我在下面尝试的代码没有成功

mysql> SELECT DISTINCT
    t.start_date,
    t.end_date
FROM
    `calendar_recovery_interval_hour_2020` t,
    `stable_2020` m
WHERE
    m.`sdatetime` NOT BETWEEN Cast(t.start_date AS DateTime)
AND Cast(t.end_date AS DateTime);
+---------------------+---------------------+
| start_date          | end_date            |
+---------------------+---------------------+
| 2020-04-14 00:00:00 | 2020-04-14 00:59:00 |
| 2020-04-14 01:00:00 | 2020-04-14 01:59:00 |
| 2020-04-14 02:00:00 | 2020-04-14 02:59:00 |
| 2020-04-14 03:00:00 | 2020-04-14 03:59:00 |
| 2020-04-14 04:00:00 | 2020-04-14 04:59:00 |
| 2020-04-14 05:00:00 | 2020-04-14 05:59:00 |
| 2020-04-14 06:00:00 | 2020-04-14 06:59:00 |
| 2020-04-14 07:00:00 | 2020-04-14 07:59:00 |
| 2020-04-14 08:00:00 | 2020-04-14 08:59:00 |
| 2020-04-14 09:00:00 | 2020-04-14 09:59:00 |
| 2020-04-14 10:00:00 | 2020-04-14 10:59:00 |
| 2020-04-14 11:00:00 | 2020-04-14 11:59:00 |
| 2020-04-14 12:00:00 | 2020-04-14 12:59:00 |
| 2020-04-14 13:00:00 | 2020-04-14 13:59:00 |
| 2020-04-14 14:00:00 | 2020-04-14 14:59:00 |
| 2020-04-14 15:00:00 | 2020-04-14 15:59:00 |
| 2020-04-14 16:00:00 | 2020-04-14 16:59:00 |
| 2020-04-14 17:00:00 | 2020-04-14 17:59:00 |
| 2020-04-14 18:00:00 | 2020-04-14 18:59:00 |
| 2020-04-14 19:00:00 | 2020-04-14 19:59:00 |
| 2020-04-14 20:00:00 | 2020-04-14 20:59:00 |
| 2020-04-14 21:00:00 | 2020-04-14 21:59:00 |
| 2020-04-14 22:00:00 | 2020-04-14 22:59:00 |
| 2020-04-14 23:00:00 | 2020-04-14 23:59:00 |
+---------------------+---------------------+
24 rows in set

和

mysql> SELECT
    start_date,
    end_date
FROM
    `calendar_recovery_interval_hour_2020` t
WHERE
    EXISTS (
        SELECT
            1
        FROM
            `stable_2020` m
        WHERE
            m.`sdatetime` NOT BETWEEN t.start_date
        AND t.end_date
        ORDER BY
            m.`sdatetime` DESC
    );
+---------------------+---------------------+
| start_date          | end_date            |
+---------------------+---------------------+
| 2020-04-14 00:00:00 | 2020-04-14 00:59:00 |
| 2020-04-14 01:00:00 | 2020-04-14 01:59:00 |
| 2020-04-14 02:00:00 | 2020-04-14 02:59:00 |
| 2020-04-14 03:00:00 | 2020-04-14 03:59:00 |
| 2020-04-14 04:00:00 | 2020-04-14 04:59:00 |
| 2020-04-14 05:00:00 | 2020-04-14 05:59:00 |
| 2020-04-14 06:00:00 | 2020-04-14 06:59:00 |
| 2020-04-14 07:00:00 | 2020-04-14 07:59:00 |
| 2020-04-14 08:00:00 | 2020-04-14 08:59:00 |
| 2020-04-14 09:00:00 | 2020-04-14 09:59:00 |
| 2020-04-14 10:00:00 | 2020-04-14 10:59:00 |
| 2020-04-14 11:00:00 | 2020-04-14 11:59:00 |
| 2020-04-14 12:00:00 | 2020-04-14 12:59:00 |
| 2020-04-14 13:00:00 | 2020-04-14 13:59:00 |
| 2020-04-14 14:00:00 | 2020-04-14 14:59:00 |
| 2020-04-14 15:00:00 | 2020-04-14 15:59:00 |
| 2020-04-14 16:00:00 | 2020-04-14 16:59:00 |
| 2020-04-14 17:00:00 | 2020-04-14 17:59:00 |
| 2020-04-14 18:00:00 | 2020-04-14 18:59:00 |
| 2020-04-14 19:00:00 | 2020-04-14 19:59:00 |
| 2020-04-14 20:00:00 | 2020-04-14 20:59:00 |
| 2020-04-14 21:00:00 | 2020-04-14 21:59:00 |
| 2020-04-14 22:00:00 | 2020-04-14 22:59:00 |
| 2020-04-14 23:00:00 | 2020-04-14 23:59:00 |
+---------------------+---------------------+
24 rows in set

我的结构表如下

DROP TABLE IF EXISTS `stable_2020`;
CREATE TABLE `stable_2020` (
  `STUX` int(11) DEFAULT NULL,
  `sdatetime` datetime DEFAULT NULL,
  `sID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`sID`),
  UNIQUE KEY `ukey` (`STUX`,`sdatetime`) USING BTREE,
  KEY `sdatetime` (`sdatetime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of stable_2020
-- ----------------------------
INSERT INTO `stable_2020` VALUES ('14', '2020-04-14 01:09:00', '1');
INSERT INTO `stable_2020` VALUES ('14', '2020-04-14 01:59:00', '2');
INSERT INTO `stable_2020` VALUES ('14', '2020-04-14 02:02:00', '3');
INSERT INTO `stable_2020` VALUES ('14', '2020-04-14 02:52:00', '4');

DROP TABLE IF EXISTS `calendar_recovery_interval_hour_2020`;
CREATE TABLE `calendar_recovery_interval_hour_2020` (
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `sID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`sID`)
) ENGINE=InnoDB CHARSET=latin1;

-- ----------------------------
-- Records of calendar_recovery_interval_hour_2020
-- ----------------------------
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 00:00:00', '2020-04-14 00:59:00', '1');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 01:00:00', '2020-04-14 01:59:00', '2');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 02:00:00', '2020-04-14 02:59:00', '3');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 03:00:00', '2020-04-14 03:59:00', '4');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 04:00:00', '2020-04-14 04:59:00', '5');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 05:00:00', '2020-04-14 05:59:00', '6');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 06:00:00', '2020-04-14 06:59:00', '7');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 07:00:00', '2020-04-14 07:59:00', '8');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 08:00:00', '2020-04-14 08:59:00', '9');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 09:00:00', '2020-04-14 09:59:00', '10');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 10:00:00', '2020-04-14 10:59:00', '11');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 11:00:00', '2020-04-14 11:59:00', '12');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 12:00:00', '2020-04-14 12:59:00', '13');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 13:00:00', '2020-04-14 13:59:00', '14');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 14:00:00', '2020-04-14 14:59:00', '15');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 15:00:00', '2020-04-14 15:59:00', '16');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 16:00:00', '2020-04-14 16:59:00', '17');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 17:00:00', '2020-04-14 17:59:00', '18');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 18:00:00', '2020-04-14 18:59:00', '19');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 19:00:00', '2020-04-14 19:59:00', '20');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 20:00:00', '2020-04-14 20:59:00', '21');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 21:00:00', '2020-04-14 21:59:00', '22');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 22:00:00', '2020-04-14 22:59:00', '23');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 23:00:00', '2020-04-14 23:59:00', '24');

更新

mysql> SELECT DISTINCT
    t.start_date,
    t.end_date
FROM
    `calendar_recovery_interval_hour_2020` t,
    `stable_2020` m
WHERE
    Cast(
        DATE_FORMAT(
            m.`sdatetime`,
            '%Y-%m-%d %H'
        ) AS DateTime
    ) NOT BETWEEN Cast(
        DATE_FORMAT(t.start_date, '%Y-%m-%d %H') AS DateTime
    )
AND Cast(
    DATE_FORMAT(t.end_date, '%Y-%m-%d %H') AS DateTime
);
+---------------------+---------------------+
| start_date          | end_date            |
+---------------------+---------------------+
| 2020-04-14 00:00:00 | 2020-04-14 00:59:00 |
| 2020-04-14 01:00:00 | 2020-04-14 01:59:00 |
| 2020-04-14 02:00:00 | 2020-04-14 02:59:00 |
| 2020-04-14 03:00:00 | 2020-04-14 03:59:00 |
| 2020-04-14 04:00:00 | 2020-04-14 04:59:00 |
| 2020-04-14 05:00:00 | 2020-04-14 05:59:00 |
| 2020-04-14 06:00:00 | 2020-04-14 06:59:00 |
| 2020-04-14 07:00:00 | 2020-04-14 07:59:00 |
| 2020-04-14 08:00:00 | 2020-04-14 08:59:00 |
| 2020-04-14 09:00:00 | 2020-04-14 09:59:00 |
| 2020-04-14 10:00:00 | 2020-04-14 10:59:00 |
| 2020-04-14 11:00:00 | 2020-04-14 11:59:00 |
| 2020-04-14 12:00:00 | 2020-04-14 12:59:00 |
| 2020-04-14 13:00:00 | 2020-04-14 13:59:00 |
| 2020-04-14 14:00:00 | 2020-04-14 14:59:00 |
| 2020-04-14 15:00:00 | 2020-04-14 15:59:00 |
| 2020-04-14 16:00:00 | 2020-04-14 16:59:00 |
| 2020-04-14 17:00:00 | 2020-04-14 17:59:00 |
| 2020-04-14 18:00:00 | 2020-04-14 18:59:00 |
| 2020-04-14 19:00:00 | 2020-04-14 19:59:00 |
| 2020-04-14 20:00:00 | 2020-04-14 20:59:00 |
| 2020-04-14 21:00:00 | 2020-04-14 21:59:00 |
| 2020-04-14 22:00:00 | 2020-04-14 22:59:00 |
| 2020-04-14 23:00:00 | 2020-04-14 23:59:00 |
+---------------------+---------------------+
24 rows in set
sqlmysqlJoinDate

3条答案

按热度按时间
yquaqz18

yquaqz181#

你可以离开加入 calendar_recovery_interval_hour_2020 至 stable_2020 并返回不匹配的行：

SELECT c.start_date, c.end_date
FROM calendar_recovery_interval_hour_2020 c LEFT JOIN stable_2020 s
ON s.sdatetime BETWEEN c.start_date AND c.end_date
WHERE s.SID IS NULL

请看演示。
结果：

| start_date          | end_date            |
| ------------------- | ------------------- |
| 2020-04-14 00:00:00 | 2020-04-14 00:59:00 |
| 2020-04-14 03:00:00 | 2020-04-14 03:59:00 |
| 2020-04-14 04:00:00 | 2020-04-14 04:59:00 |
| 2020-04-14 05:00:00 | 2020-04-14 05:59:00 |
| 2020-04-14 06:00:00 | 2020-04-14 06:59:00 |
| 2020-04-14 07:00:00 | 2020-04-14 07:59:00 |
| 2020-04-14 08:00:00 | 2020-04-14 08:59:00 |
| 2020-04-14 09:00:00 | 2020-04-14 09:59:00 |
| 2020-04-14 10:00:00 | 2020-04-14 10:59:00 |
| 2020-04-14 11:00:00 | 2020-04-14 11:59:00 |
| 2020-04-14 12:00:00 | 2020-04-14 12:59:00 |
| 2020-04-14 13:00:00 | 2020-04-14 13:59:00 |
| 2020-04-14 14:00:00 | 2020-04-14 14:59:00 |
| 2020-04-14 15:00:00 | 2020-04-14 15:59:00 |
| 2020-04-14 16:00:00 | 2020-04-14 16:59:00 |
| 2020-04-14 17:00:00 | 2020-04-14 17:59:00 |
| 2020-04-14 18:00:00 | 2020-04-14 18:59:00 |
| 2020-04-14 19:00:00 | 2020-04-14 19:59:00 |
| 2020-04-14 20:00:00 | 2020-04-14 20:59:00 |
| 2020-04-14 21:00:00 | 2020-04-14 21:59:00 |
| 2020-04-14 22:00:00 | 2020-04-14 22:59:00 |
| 2020-04-14 23:00:00 | 2020-04-14 23:59:00 |
赞(0）分享  回复(0）举报  2021-08-09
rhfm7lfc

rhfm7lfc2#

我会用 not exists 为此：

select c.*
from calendar_recovery_interval_hour_2020 
where not exists (
    select 1 from stable_2020 s where s.datetime nto between c.start_date and c.end_date
)

带索引的 stable_2020(datetime) ，此查询应具有良好的性能。

赞(0）分享  回复(0）举报  2021-08-09
yhived7q

yhived7q3#

你为什么不放弃分和秒，把日期时间和时间做个比较呢。所以你不需要介于两者之间。
这就是我的意思：在“stable\u 2020”表上做一个左连接，其中日期和时间相同。并且只给出在“stable\ U 2020”中没有记录的结果

SELECT 
    t1.start_date, t1.end_date
FROM 
    calendar_recovery_interval_hour_2020 AS t1
LEFT JOIN 
    stable_2020 AS t2
ON 
    (DATE(t1.start_date) = DATE(t2.sdatetime)) 
    AND (HOUR(t1.start_date) = HOUR(t2.sdatetime))
WHERE 
    t2.SID IS NULL
赞(0）分享  回复(0）举报  2021-08-09
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com