mysql - 仅适用于某些情况的查询

转载作者：可可西里更新时间：2023-11-01 07:45:38

我有一个类似于以下结构的表:

City        start_date             end_date
Paris       1995-01-01 00:00:00    1997-10-01 23:59:59
Paris       1997-10-02 00:00:00    0001-01-01 00:00:00
Paris       2013-01-25 00:00:00    0001-01-01 00:00:00
Paris       2015-04-25 00:00:00    0001-01-01 00:00:00
Berlin      2014-11-01 00:00:00    0001-01-01 00:00:00
Berlin      2014-06-01 00:00:00    0001-01-01 00:00:00
Berlin      2015-09-11 00:00:00    0001-01-01 00:00:00
Berlin      2015-10-01 00:00:00    0001-01-01 00:00:00
Milan       2001-01-01 00:00:00    0001-01-01 00:00:00
Milan       2005-10-02 00:00:00    2006-10-02 23:59:59
Milan       2006-10-03 00:00:00    2015-04-24 23:59:59
Milan       2015-04-25 00:00:00    0001-01-01 00:00:00

数据包含基于城市的开始日期和结束日期的历史 View 。一个城市的最新记录应该是开始日期最长的记录，结束日期为'0001-01-01 00:00:00'，表示还没有结束日期。

我需要清理这些数据并确保每个城市的历史记录都有结束日期比下一条记录的开始日期早一秒，只有在 end_date 设置为“0001-”的情况下01-01 00:00:00'。因此，在 end_date 有实际日期的情况下，该日期将被忽略。此外，城市的 start_date 最近的记录不需要修改 end_date。

结果表应该是这样的:

City        start_date             end_date
Paris       1995-01-01 00:00:00    1997-10-01 23:59:59
Paris       1997-10-02 00:00:00    2013-01-24 23:59:59
Paris       2013-01-25 00:00:00    2015-04-24 23:59:59
Paris       2015-04-25 00:00:00    0001-01-01 00:00:00
Berlin      2014-11-01 00:00:00    2014-05-31 23:59:59
Berlin      2014-06-01 00:00:00    2015-09-10 23:59:59
Berlin      2015-09-11 00:00:00    2015-09-30 23:59:59
Berlin      2015-10-01 00:00:00    0001-01-01 23:59:59
Milan       2001-01-01 00:00:00    2005-10-01 23:59:59
Milan       2005-10-02 00:00:00    2006-10-02 23:59:59
Milan       2006-10-03 00:00:00    2015-04-24 23:59:59
Milan       2015-04-25 00:00:00    0001-01-01 00:00:00

我已经尝试了以下用户在 this question 中建议的脚本.

update test join
       (select t.*,
               (select min(start_date)
                from test t2
                where t2.city = t.city and
                      t2.start_date > t.start_date
                order by t2.start_date
                limit 1
               ) as next_start_date
        from test t
       ) tt
       on tt.city = test.city and tt.start_date = test.start_date
    set test.end_date = date_sub(tt.next_start_date, interval 1 second)
where test.end_date = '0001-01-01' and
      next_start_date is not null;

不幸的是，从柏林记录开始，一些结束日期不是预期的(例如 ID 号 5 和 6)。然而，其他人正在按预期出现。如下所示:

以下是能够复制的创建和插入语句:

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `city` varchar(50) DEFAULT NULL,
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

INSERT INTO test (city, start_date, end_date) VALUES ('Paris','1995-01-01 00:00:00','1997-10-01 23:59:59');
INSERT INTO test (city, start_date, end_date) VALUES ('Paris','1997-10-02 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Paris','2013-01-25 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Paris','2015-04-25 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Berlin','2014-11-01 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Berlin','2014-06-01 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Berlin','2015-09-11 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Berlin','2015-10-01 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Milan','2001-01-01 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Milan','2005-10-02 00:00:00','2006-10-02 23:59:59');
INSERT INTO test (city, start_date, end_date) VALUES ('Milan','2006-10-03 00:00:00','2015-04-24 23:59:59');
INSERT INTO test (city, start_date, end_date) VALUES ('Milan','2015-04-25 00:00:00','0001-01-01 00:00:00');

最佳答案

-- query wanted
UPDATE test t1 INNER JOIN
    (SELECT *, @id := @id + 1 AS new_id 
     FROM test CROSS JOIN (SELECT @id := 0) param
     ORDER BY city, start_date) t2
     ON t1.city = t2.city AND t1.start_date = t2.start_date
     INNER JOIN 
    (SELECT *, @id2 := @id2 + 1 AS new_id 
     FROM test CROSS JOIN (SELECT @id2 := 0) param
     ORDER BY city, start_date) t3 
     ON t2.new_id + 1 = t3.new_id AND t2.city = t3.city
SET t1.end_date = DATE_SUB(t3.start_date, INTERVAL 1 SECOND)
WHERE t1.end_date = '0001-01-01 00:00:00';

下面是完整的demo。

SQL:

-- data
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `city` varchar(50) DEFAULT NULL,
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

INSERT INTO test (city, start_date, end_date) VALUES ('Paris','1995-01-01 00:00:00','1997-10-01 23:59:59');
INSERT INTO test (city, start_date, end_date) VALUES ('Paris','1997-10-02 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Paris','2013-01-25 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Paris','2015-04-25 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Berlin','2014-11-01 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Berlin','2014-06-01 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Berlin','2015-09-11 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Berlin','2015-10-01 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Milan','2001-01-01 00:00:00','0001-01-01 00:00:00');
INSERT INTO test (city, start_date, end_date) VALUES ('Milan','2005-10-02 00:00:00','2006-10-02 23:59:59');
INSERT INTO test (city, start_date, end_date) VALUES ('Milan','2006-10-03 00:00:00','2015-04-24 23:59:59');
INSERT INTO test (city, start_date, end_date) VALUES ('Milan','2015-04-25 00:00:00','0001-01-01 00:00:00');
select * from test;

-- query wanted
UPDATE test t1 INNER JOIN
    (SELECT *, @id := @id + 1 AS new_id 
     FROM test CROSS JOIN (SELECT @id := 0) param
     ORDER BY city, start_date) t2
     ON t1.city = t2.city AND t1.start_date = t2.start_date
     INNER JOIN 
    (SELECT *, @id2 := @id2 + 1 AS new_id 
     FROM test CROSS JOIN (SELECT @id2 := 0) param
     ORDER BY city, start_date) t3 
     ON t2.new_id + 1 = t3.new_id AND t2.city = t3.city
SET t1.end_date = DATE_SUB(t3.start_date, INTERVAL 1 SECOND)
WHERE t1.end_date = '0001-01-01 00:00:00';

select * from test;

输出:

mysql> -- query wanted
mysql> UPDATE test t1 INNER JOIN
    -> (SELECT *, @id := @id + 1 AS new_id
    ->  FROM test CROSS JOIN (SELECT @id := 0) param
    ->  ORDER BY city, start_date) t2
    ->  ON t1.city = t2.city AND t1.start_date = t2.start_date
    ->  INNER JOIN
    -> (SELECT *, @id2 := @id2 + 1 AS new_id
    ->  FROM test CROSS JOIN (SELECT @id2 := 0) param
    ->  ORDER BY city, start_date) t3
    ->  ON t2.new_id + 1 = t3.new_id AND t2.city = t3.city
    -> SET t1.end_date = DATE_SUB(t3.start_date, INTERVAL 1 SECOND)
    -> WHERE t1.end_date = '0001-01-01 00:00:00';
rom tesQuery OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> select * from test;
+----+--------+---------------------+---------------------+
| id | city   | start_date          | end_date            |
+----+--------+---------------------+---------------------+
| 13 | Paris  | 1995-01-01 00:00:00 | 1997-10-01 23:59:59 |
| 14 | Paris  | 1997-10-02 00:00:00 | 2013-01-24 23:59:59 |
| 15 | Paris  | 2013-01-25 00:00:00 | 2015-04-24 23:59:59 |
| 16 | Paris  | 2015-04-25 00:00:00 | 0001-01-01 00:00:00 |
| 17 | Berlin | 2014-11-01 00:00:00 | 2014-05-31 23:59:59 |
| 18 | Berlin | 2014-06-01 00:00:00 | 2015-09-10 23:59:59 |
| 19 | Berlin | 2015-09-11 00:00:00 | 2015-09-30 23:59:59 |
| 20 | Berlin | 2015-10-01 00:00:00 | 0001-01-01 00:00:00 |
| 21 | Milan  | 2001-01-01 00:00:00 | 2005-10-01 23:59:59 |
| 22 | Milan  | 2005-10-02 00:00:00 | 2006-10-02 23:59:59 |
| 23 | Milan  | 2006-10-03 00:00:00 | 2015-04-24 23:59:59 |
| 24 | Milan  | 2015-04-25 00:00:00 | 0001-01-01 00:00:00 |
+----+--------+---------------------+---------------------+
12 rows in set (0.00 sec)

关于mysql - 仅适用于某些情况的查询，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/36125799/

文章推荐： mysql - 如何比较 MySQL 5.6 中的 JSON-like 文本？

文章推荐： java - AdapterView 不支持错误 : addView(View, LayoutParams)

文章推荐： c# - 为什么在此示例中 LINQ 更快

internet-explorer - 仅 Windows 7，仅 IE，认为我的网站证书有问题？
我仅在 WIN7 PC 上收到此通知，仅使用 IE。 Firefox 总是很好，旧版 Windows 上的 IE 似乎也不错。这让我大吃一惊，我不知道为什么 IE 认为 SSL 证书有问题。有没有人以
wix - 升级期间未复制文件(仅)
概述对于我产品的新版本 v1.9.0，我创建了一个新的 MSI 安装程序。该应用程序的先前版本是 v1.7.0。卸载旧版本然后安装新版本工作正常。但是当我尝试使用 v1.9.0 安装程序更新旧版
仅 JavaScript 按钮按下时平滑滚动
该网站有一个全高图像启动。更多内容位于首屏下方，图像底部有一个“滚动”元素，以提示用户发现其余内容。单击后，我成功地使网站向下滚动 300 像素。然而，我想顺利地做到这一点。这是我当前的代码: w
javascript - 动态创建类名。仅
var i = 0; function Myfunc() { var newdiv = document.createElement('div'); var el = document
仅 JavaScript 图像悬停
这纯粹是为了学习目的；我知道 CSS 将是这种情况下的首选方法。我知道在 JavaScript 中，您可以使用内联事件处理将鼠标悬停在图像上，如下所示: 我知道您可以在您的站点中安装 jQuery
仅 curl 发布数据和标题
我只想从curl请求中获取 header curl -I www.google.com 一切都很棒。现在我想这样做，但也传递发布数据: curl -I -d'test=test' www.google
javascript - 仅 getElementsByName？
以下代码旨在更改一个字段的颜色: Untitled Document var bkColor =
仅 Grep 第一个匹配项并停止
我正在使用 grep 递归搜索目录，并使用以下参数希望只返回第一个匹配项。不幸的是，它返回了不止一个——事实上，我上次查看时返回了两个。似乎我有太多的争论，尤其是没有得到想要的结果。 :-/ # gr
仅 grep 当前目录
我只想搜索当前目录中的所有文件。我试过这个 grep foo * 但我收到此错误 grep: bar: Is a directory 我也尝试过这个 grep -r foo 但这也在搜索子目录。最佳
JavaFx如何打印文本(仅)收据？
我正在构建一个销售点应用程序，我想打印一张收据。问题是我使用的打印机无法打印纯文本的任何图形，我在 javafx 中只能找到使用 Print API 打印节点或使用像 jasper 这样都包含图形的报
仅 Java 操作系统
是否有任何操作系统在完全加载时仅提供用于控制台应用程序执行的 java 环境？理想情况下，它会在加载时自动启动程序最佳答案这是一个名称为:JavaOS 的东西从我的角度来看，更好的方法是安装一个
仅 MySQL 转储还原增量
在工作中，我们有一个每晚执行 mysql 数据转储的脚本。对于开发，我们通常需要使用来自最近转储的数据。一段时间以来，我们一直每天都进行数据库还原，但现在我们已经到了每天还原花费近一个小时的地步。有没
javascript - 需要禁用下拉菜单的淡出功能(仅)
我的移动模式菜单有问题。 onClick 它淡出。我想保留此设置，但我不希望它在单击下拉部分时淡出。这是链接:http://jsfiddle.net/zLLzrs6b/3/感谢您的帮助! html:
仅 css 在屏幕尺寸变化时调整图像大小的灵活布局
经过大量研究和反复试验，我谦虚地向各位 CSS 专家寻求帮助。这就是我需要的: 我有两张图片:titlelogo 和 newlogo。在全屏模式下，newlogo 需要在左边，titlelogo 在
javascript - 选择具有特定属性的标签(仅)
这个问题在这里已经有了答案: Exclusive CSS selector (3 个答案) 关闭 3 年前。我的文档结构如下: ... ... something something someth
html - 强制可变宽度表格列内容以最大宽度换行*仅*
我有一个具有以下要求的表: 所有列的宽度必须可变所有列的宽度不得超过必要的宽度所有单元格必须保留空白(white-space:pre/pre-wrap) 当(且仅当)超过最大定义宽度 (1000p
c++ - 具有特殊字符的数字的正则表达式**仅**
我正在寻找一个正则表达式来仅匹配具有特殊字符且大小为4+ 的数字字符串。我对此处发布的问题做了一些评论: 测试网站: http://regexlib.com/RETester.aspx 1- re
仅 CSS 灯箱解决方案
我正在为我的元素开发一个纯 CSS 灯箱解决方案。我用谷歌搜索了它，但到目前为止只找到了部分解决方案。我正在寻找这些功能: 显示任意宽任意高的内容(无固定高/宽) 垂直居中和水平居中如果内容宽度和
仅 CSS 网格布局
出于各种原因，我目前正在尝试使用 HTML/CSS 创建网格布局(我知道 Bootstrap 等，但在这种情况下没有选择，而且我无法添加标记元素)。我有以下代码(容器 div，每次都有一个带有 ul
仅 Java 字符串格式小数
有没有办法使用String.format()格式化 double 以仅获取小数？ System.out.println(String.format("%.2f", 1.23456d)); 正如预期的那

可可西里

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

mysql - 仅适用于某些情况的查询

SQL:

输出: