程序员经验分享-hwhale

比较同一个表中的两个日期范围

2023-12-10

我有一张每家商店的销售额表，如下所示：

SQL> select * from sales;

        ID ID_STORE DATE       TOTAL
---------- -------- ---------- -------------------------------
         1        1 2010-01-01    500.00
         2        1 2010-01-02    185.00
         3        1 2010-01-03    135.00
         4        1 2009-01-01    165.00
         5        1 2009-01-02    175.00
         6        5 2010-01-01    130.00
         7        5 2010-01-02    135.00
         8        5 2010-01-03    130.00
         9        6 2010-01-01    100.00
         10       6 2010-01-02     12.00
         11       6 2010-01-03     85.00
         12       6 2009-01-01    135.00
         13       6 2009-01-02    400.00
         14       6 2009-01-07     21.00
         15       6 2009-01-08     45.00
         16       8 2009-01-09    123.00
         17       8 2009-01-10    581.00

17 rows selected.

我需要做的是比较该表中的两个日期范围。假设我需要知道2009年1月1日至2009年1月10日与2010年1月1日至2010年1月10日之间的销售额差异。

我想构建一个返回类似以下内容的查询：

ID_STORE_A DATE_A     TOTAL_A   ID_STORE_B DATE_B     TOTAL_B
---------- ---------- --------- ---------- ---------- -------------------
         1 2010-01-01    500.00          1 2009-01-01    165.00
         1 2010-01-02    185.00          1 2009-01-02    175.00
         1 2010-01-03    135.00          1 NULL          NULL

         5 2010-01-01    130.00          5 NULL          NULL
         5 2010-01-02    135.00          5 NULL          NULL
         5 2010-01-03    130.00          5 NULL          NULL

         6 2010-01-01    100.00          6 2009-01-01    135.00
         6 2010-01-02     12.00          6 2009-01-02    400.00
         6 2010-01-03     85.00          6 NULL          NULL
         6 NULL          NULL            6 2009-01-07     21.00
         6 NULL          NULL            6 2009-01-08     45.00
         6 NULL          NULL            8 2009-01-09    123.00
         6 NULL          NULL            8 2009-01-10    581.00

因此，即使某个范围内没有销售，也应该用 NULL 填充空白。

到目前为止，我已经提出了这个快速查询，但从 sales 到 sales2 的“日期”有时每行都不同：

SELECT sales.*, sales2.*
  FROM sales
  LEFT JOIN sales AS sales2 
    ON (sales.id_store=sales2.id_store)
 WHERE sales.date >= '2010-01-01' 
   AND sales.date <= '2010-01-10' 
   AND sales2.date >= '2009-01-01' 
   AND sales2.date <= '2009-01-10' 
ORDER BY sales.id_store ASC, sales.date ASC, sales2.date ASC

我缺少什么？


使用 IBM Informix Dynamic Server 11.50.FC6，我可以使用此 SQL 序列来获取您需要的结果：

Setup

CREATE TABLE sales
(
    id       INTEGER NOT NULL,
    id_store INTEGER NOT NULL,
    date     DATE NOT NULL,
    total    DECIMAL(10,2) NOT NULL
);

INSERT INTO sales VALUES( 1, 1, '2010-01-01', 500.00);
INSERT INTO sales VALUES( 2, 1, '2010-01-02', 185.00);
INSERT INTO sales VALUES( 3, 1, '2010-01-03', 135.00);
INSERT INTO sales VALUES( 4, 1, '2009-01-01', 165.00);
INSERT INTO sales VALUES( 5, 1, '2009-01-02', 175.00);
INSERT INTO sales VALUES( 6, 5, '2010-01-01', 130.00);
INSERT INTO sales VALUES( 7, 5, '2010-01-02', 135.00);
INSERT INTO sales VALUES( 8, 5, '2010-01-03', 130.00);
INSERT INTO sales VALUES( 9, 6, '2010-01-01', 100.00);
INSERT INTO sales VALUES(10, 6, '2010-01-02',  12.00);
INSERT INTO sales VALUES(11, 6, '2010-01-03',  85.00);
INSERT INTO sales VALUES(12, 6, '2009-01-01', 135.00);
INSERT INTO sales VALUES(13, 6, '2009-01-02', 400.00);
INSERT INTO sales VALUES(14, 6, '2009-01-07',  21.00);
INSERT INTO sales VALUES(15, 6, '2009-01-08',  45.00);
INSERT INTO sales VALUES(16, 8, '2009-01-09', 123.00);
INSERT INTO sales VALUES(17, 8, '2009-01-10', 581.00);

Query

SELECT *
  FROM (SELECT s1.id AS s1id,
               NVL(s1.id_store, s2.id_store) AS s1store,
               NVL(s1.date, MDY(MONTH(s2.date), DAY(s2.date),
                                YEAR(s2.date)+1)) AS s1date,
               s1.total AS s1total,
               s2.id AS s2id,
               NVL(s2.id_store, s1.id_store) AS s2store,
               NVL(s2.date, MDY(MONTH(s1.date), DAY(s1.date),
                                YEAR(s1.date)-1)) AS s2date,
               s2.total AS s2total
          FROM sales AS s1 FULL JOIN sales AS s2
            ON s1.id_store = s2.id_store
           AND s1.date BETWEEN '2010-01-01' AND '2010-01-10'
           AND s2.date BETWEEN '2009-01-01' AND '2009-01-10'
           AND DAY(s1.date)   = DAY(s2.date)
           AND MONTH(s1.date) = MONTH(s2.date)
       ) AS s3
 WHERE s1_date BETWEEN '2010-01-01' AND '2010-01-10'
   AND s2_date BETWEEN '2009-01-01' AND '2009-01-10'
 ORDER BY s1_id_store ASC, s1_date ASC;

Result

s1id s1store  s1date     s1total  s2id s2store  s2date     s2total
 1       1    2010-01-01  500.00   4       1    2009-01-01  165.00
 2       1    2010-01-02  185.00   5       1    2009-01-02  175.00
 3       1    2010-01-03  135.00           1    2009-01-03             
 6       5    2010-01-01  130.00           5    2009-01-01             
 7       5    2010-01-02  135.00           5    2009-01-02             
 8       5    2010-01-03  130.00           5    2009-01-03             
 9       6    2010-01-01  100.00  12       6    2009-01-01  135.00
10       6    2010-01-02   12.00  13       6    2009-01-02  400.00
11       6    2010-01-03   85.00           6    2009-01-03             
         6    2010-01-07          14       6    2009-01-07   21.00
         6    2010-01-08          15       6    2009-01-08   45.00
         8    2010-01-09          16       8    2009-01-09  123.00
         8    2010-01-10          17       8    2009-01-10  581.00

解释

我们进行了大量的实验才得到“正确”的结果。 Informix 有一个 DATE 构造函数 MDY()，它接受三个整数参数：月、日和年（名称是助记符）。它还具有三个分析函数：DAY()、MONTH() 和 YEAR()，它们返回日期参数的日、月和年。使用 FULL JOIN 的内部查询为您提供左侧和右侧均包含空值的结果。 ON子句中的5部分标准似乎是必要的；否则，外部查询中的条件必须更加复杂和混乱 - 如果它可以正常工作的话。然后，外部选择中的标准可确保选择正确的数据。内部查询中 NVL() 表达式的一个优点是商店 ID 列相同且不为空，并且日期列都不为空，因此 order by 子句可以更简单 - 在商店 ID 和任一日期列上。

在 Informix 中，还可以将日期表达式重写为：

NVL(s1.date, s2.date + 1 UNITS YEAR)
NVL(s2.date, s1.date - 1 UNITS YEAR)

实际上，使用该表示法在幕后进行了多种类型转换，但它给出了相同的结果，并且额外的计算可能并不那么重要。

Informix 中的等待也存在一个问题；您不能在任何 2 月 29 日之间添加或减去 1 年 - 因为下一年或上一年没有 2 月 29 日。您需要小心处理您的数据；如果不是，您最终可能会比较 2008-02-29 与 2009-02-28 的数据（以及比较 2008-02-28 与 2009-02-28 的数据）。有一个称为“复式记账”的过程，但这不是它的含义，如果“2008-02-29 加 1 年”是 2009-02-28，您的计算可能会混乱。 Informix 产生错误；这并没有多大帮助。您可能会编写一个存储过程来返回 2008-02-29 加 1 年的 NULL，因为没有任何日期可以与其销售额进行比较。

您应该能够相当轻松地使日期算法适应 MySQL；其余代码不需要更改。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

mysql

null

date

Compare

range

比较同一个表中的两个日期范围 的相关文章

  • Codeigniter 加入多个条件

    我正在使用 Codeigniter Active Records 课程 我想加入我的users与我的桌子clients表 这样我就可以显示用户的 真实 姓名 而不仅仅是他们的 ID 这是什么clients表看起来像 示例 列 a 1 a 2

  • 无法使用 Django 应用程序从容器连接到 MySQL docker 容器

    当我尝试从运行 Django 应用程序的 docker 容器连接到运行 MySQL 的容器时 出现以下错误 django db utils OperationalError 2003 Can t connect to MySQL serve

  • C#：SQL 查询生成器类

    在哪里可以找到好的 SQL 查询构建器类 我只需要一个简单的类来构建 SQL 字符串 仅此而已 我需要它用于 C 和 MySql 我真的不需要像 Linq 或 NHibernate 这样的东西 谢谢 由于 Google 将我引导至此页面 我

  • 将 UPDATE 转换为 INSERT INTO ON DUPLICATE KEY UPDATE 语句

    我有这个 UPDATE MySQL 语句 效果很好 UPDATE table1 Inner Join table2 ON table2 id table1 gw id SET table1 field1 1 table1 field2 2

  • 通过连接从两个表中删除？

    我有两个表如下 tbl1 tbl2 id article id title image whole news tags author older datetime 其中 tbl1 id gt tbl2 article id 如何从两个表中删

  • MySQL 服务器未启动

    当我做 mysql u root p并输入my password这就是我得到的 错误 2002 HY000 无法通过套接字 var run mysqld mysqld sock 连接到本地 MySQL 服务器 2 所以我输入 systemc

  • 解析 (yyyy-MM-dd) 格式的字符串日期

    我有一个 2013 09 18 形式的字符串 我想将其转换为 java util Date 我正在做这个 SimpleDateFormat sdf new SimpleDateFormat yyyy MM dd Date converted

  • 社交应用程序的数据库设计和优化注意事项

    通常的情况 我有一个简单的应用程序 允许人们上传照片并关注其他人 因此 每个用户都会有类似 墙 或 活动源 的东西 他或她可以在其中看到他 她的朋友 他或她关注的人 上传的最新照片 大多数功能都很容易实现 然而 当涉及到这个历史活动源时 由

  • 比较表中的行以了解字段之间的差异

    我有一个包含 20 多列的表 客户端 其中大部分是历史数据 就像是 id clientID field1 field2 etc updateDate 如果我的数据如下所示 10 12 A A 2009 03 01 11 12 A B 200

  • Mysql innoDB 不断崩溃[关闭]

    Closed 这个问题不符合堆栈溢出指南 help closed questions 目前不接受答案 我的数据库 mysql 服务器不断崩溃 重新启动 我不知道该怎么办 我不断在 dbname org err 文件中收到以下内容 13120

  • PDO SQLSRV 和 PDO MySQL 在获取 int 或 float 时返回字符串

    当您获取时 PDO MS SQL Server 和 PDO MySQL 都会返回一个字符串数组 即使列的 SQL 类型本应是数字类型 例如 int 或 float 我设法解决了这个问题 但我想了解为什么它们一开始就这样设计 是不是因为PDO

  • 无法启动 MySQL 服务器 - 控制进程退出并出现错误代码

    我的 mysql 服务器停止后无法启动 命令使用 sudo etc init d mysql restart Error 重新启动 mysql 通过 systemctl mysql serviceJob for mysql service

  • MYSQL - 查找最近的前一天

    我可以以某种方式 不使用 PHP 找到一周中最近的前一天日期吗 Like 最近的上一个星期二的日期是哪一天 CURDATE INTERVAL WEEKDAY CURDATE wday IF WEEKDAY CURDATE gt wday 0

  • PHP 绑定“bigint”数据类型（MySQLi 准备好的语句）

    studentId 57004542323382 companyOfferId 7 sql INSERT INTO studentPlacement companyOfferId studentId VALUES if stmt db gt

  • MySQL Connector/C++ 库链接错误问题

    PROBLEM 好吧 我一直在尝试遵循 MySQL Forge Wiki 和其他一些网站上的示例代码 这些网站提供了有关如何获得简单数据库连接的教程 但由于某种原因 我的项目总是因链接错误而失败 我可以我自己不明白为什么或如何解决它 我仍在

  • Mysql 检索所有有限制的行

    我想检索特定用户的所有行 限制为 0 x 所以我只是想问是否有任何方法可以检索 mysql 中的所有行 而不调用返回 x 的 count id 的方法 而不重载现有函数 该函数在查询中根本没有限制 与我们的 string Relace 功能

  • 如何在Mysql中仅将不同的值从一个表复制到另一个表？

    我有一个大约 2 5GB 的 MySql 数据库 表 A 具有以下列 anoid query date item rank url 我刚刚创建了另一个仅包含列的表 b query and date 我想在查询列中插入所有不同的记录 及其各自

  • 无法通过套接字“/var/lib/mysql/mysql.sock”连接到本地 MySQL 服务器 (2)

    当我尝试连接 mysql 时出现以下错误 Can t connect to local MySQL server through socket var lib mysql mysql sock 2 这个错误有解决办法吗 其背后的原因可能是什

  • 在 MySQL 中使用 COUNT 时如何返回 0 而不是 null

    我使用此查询返回存储在 sTable 中的歌曲列表以及存储在 sTable2 中的总项目数 SQL queries Get data to display sQuery SELECT SQL CALC FOUND ROWS str repl

  • Mysql 创建定义器

    我创建了一个在 CentOS Web 服务器上运行的 Intranet Web 应用程序 该应用程序使用另一个本地服务器 始终是 CentOS 作为 MySQL 数据库 在数据库内部我创建了例程 这些例程总是这样开始 CREATE DEFI

随机推荐

热门标签