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

coder 2023-10-14 原文

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

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 仅 00 date start_date sql database relational-database

有关mysql - 仅适用于某些情况的查询的更多相关文章

ruby - ECONNRESET (Whois::ConnectionError) - 尝试在 Ruby 中查询 Whois 时出错 - 2
我正在用Ruby编写一个简单的程序来检查域列表是否被占用。基本上它循环遍历列表，并使用以下函数进行检查。require'rubygems'require'whois'defcheck_domain(domain)c=Whois::Client.newc.query("google.com").available?end程序不断出错(即使我在google.com中进行硬编码)，并打印以下消息。鉴于该程序非常简单，我已经没有什么想法了-有什么建议吗？/Library/Ruby/Gems/1.8/gems/whois-2.0.2/lib/whois/server/adapters/base.
ruby-on-rails - Rails 常用字符串(用于通知和错误信息等) - 2
大约一年前，我决定确保每个包含非唯一文本的Flash通知都将从模块中的方法中获取文本。我这样做的最初原因是为了避免一遍又一遍地输入相同的字符串。如果我想更改措辞，我可以在一个地方轻松完成，而且一遍又一遍地重复同一件事而出现拼写错误的可能性也会降低。我最终得到的是这样的:moduleMessagesdefformat_error_messages(errors)errors.map{|attribute,message|"Error:#{attribute.to_s.titleize}#{message}."}enddeferror_message_could_not_find(obje
ruby - 默认情况下使选项为 false - 2
这是在Ruby中设置默认值的常用方法:classQuietByDefaultdefinitialize(opts={})@verbose=opts[:verbose]endend这是一个容易落入的陷阱:classVerboseNoMatterWhatdefinitialize(opts={})@verbose=opts[:verbose]||trueendend正确的做法是:classVerboseByDefaultdefinitialize(opts={})@verbose=opts.include?(:verbose)?opts[:verbose]:trueendend编写Verb
ruby - 在没有 sass 引擎的情况下使用 sass 颜色函数 - 2
我想在一个没有Sass引擎的类中使用Sass颜色函数。我已经在项目中使用了sassgem，所以我认为搭载会像以下一样简单:classRectangleincludeSass::Script::FunctionsdefcolorSass::Script::Color.new([0x82,0x39,0x06])enddefrender#hamlengineexecutedwithcontextofself#sothatwithintemlateicouldcall#%stop{offset:'0%',stop:{color:lighten(color)}}endend更新:参见上面的#re
ruby-on-rails - 在 Rails 和 ActiveRecord 中查询时忽略某些字段 - 2
我知道我可以指定某些字段来使用pluck查询数据库。ids=Item.where('due_at但是我想知道，是否有一种方法可以指定我想避免从数据库查询的某些字段。某种反拔？posts=Post.where(published:true).do_not_lookup(:enormous_field) 最佳答案 Model#attribute_names应该返回列/属性数组。您可以排除其中一些并传递给pluck或select方法。像这样:posts=Post.where(published:true).select(Post.attr
Ruby Sinatra 配置用于生产和开发 - 2
我已经在Sinatra上创建了应用程序，它代表了一个简单的API。我想在生产和开发上进行部署。我想在部署时选择，是开发还是生产，一些方法的逻辑应该改变，这取决于部署类型。是否有任何想法，如何完成以及解决此问题的一些示例。例子:我有代码get'/api/test'doreturn"Itisdev"end但是在部署到生产环境之后我想在运行/api/test之后看到ItisPROD如何实现？最佳答案根据SinatraDocumentation:EnvironmentscanbesetthroughtheRACK_ENVenvironm
ruby - 在不使用 RVM 的情况下在 Mac 上卸载和升级 Ruby - 2
我最近决定从我的系统中卸载RVM。在thispage提出的一些论点说服我:实际上，我的决定是，我根本不想担心Ruby的多个版本。我只想使用1.9.2-p290版本而不用担心其他任何事情。但是，当我在我的Mac上运行ruby--version时，它告诉我我的版本是1.8.7。我四处寻找如何简单地从我的Mac上卸载这个Ruby，但奇怪的是我没有找到任何东西。似乎唯一想卸载Ruby的人运行linux，而使用Mac的每个人都推荐RVM。如何从我的Mac上卸载Ruby1.8.7？我想升级到1.9.2-p290版本，并且我希望我的系统上只有一个版本。最佳答案
ruby - inverse_of 是否适用于 has_many？ - 2
当我使用has_one时，它工作得很好，但在has_many上却不行。在这里您可以看到object_id不同，因为它运行了另一个SQL来再次获取它。ruby-1.9.2-p290:001>e=Employee.create(name:'rafael',active:false)ruby-1.9.2-p290:002>b=Badge.create(number:1,employee:e)ruby-1.9.2-p290:003>a=Address.create(street:"123MarketSt",city:"SanDiego",employee:e)ruby-1.9.2-p290
使用canal同步MySQL数据到ES - 2
文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co
sql - 查询忽略时间戳日期的时间范围 - 2
我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。最佳答案您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时

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

SQL:

输出:

有关mysql - 仅适用于某些情况的查询的更多相关文章

随机推荐