如何忽略秒数按datetime字段关联两张数据库表？

表结构与数据 #

表：Carrots #

列定义：

Date (datetime) -> 示例值 "2023-09-13 23:00:12.000"
Hour (int)
Carrot (float)

示例数据：

Date                      Hour      Carrot
-------------------------------------------
2023-09-08 17:00:25.000     17       0,015
2023-09-14 03:00:16.000      3      10,793
2023-09-14 04:00:18.000      4      10,78
2023-09-15 16:00:28.000     16       0,197
2023-09-15 17:00:29.000     17      10,239

表：Prices #

列定义：

Date (DateTime) -> 示例值 "2023-09-13 23:00:00.000"
Price (float)
Hour (int)

示例数据：

Date                        Price       Hour
--------------------------------------------
2023-09-14 00:00:00.000     0,0176      0
2023-09-14 01:00:00.000     0,0192      1
2023-09-14 02:00:00.000     0,0181      2
2023-09-14 03:00:00.000     0,0177      3
2023-09-14 04:00:00.000     0,0176      4
2023-09-14 05:00:00.000     0,0181      5
2023-09-14 06:00:00.000     0,0199      6
2023-09-14 07:00:00.000     0,0318      7
2023-09-14 08:00:00.000     0,1286      8
2023-09-14 09:00:00.000     0,1257      9
2023-09-14 10:00:00.000     0,1108     10
2023-09-14 11:00:00.000     0,0992     11
2023-09-14 12:00:00.000     0,0663     12
2023-09-14 13:00:00.000     0,034      13
2023-09-14 14:00:00.000     0,0286     14
2023-09-14 15:00:00.000     0,0278     15
2023-09-14 16:00:00.000     0,0268     16
2023-09-14 17:00:00.000     0,0285     17
2023-09-14 18:00:00.000     0,1255     18
2023-09-14 19:00:00.000     0,0555     19
2023-09-14 20:00:00.000     0,0306     20
2023-09-14 21:00:00.000     0,0305     21
2023-09-14 22:00:00.000     0,0273     22
2023-09-14 23:00:00.000     0,0227     23
2023-09-15 00:00:00.000     0,0203      0
2023-09-15 01:00:00.000     0,0051      1
2023-09-15 02:00:00.000     0,0031      2  

当前问题 #

原查询仅按日期关联，导致返回大量冗余结果（Carrots表仅220条数据，查询返回5000+条）：

SELECT
    c.Date,
    c.Hour,
    c.Carrot,
    p.Price
FROM
    (SELECT DISTINCT Date, Hour, Carrot FROM Carrots) c
JOIN
    Prices p ON CONVERT(DATE, c.Date) = CONVERT(DATE, p.Date)

期望得到的关联结果：

Date                        Hour    Carrot   Price
---------------------------------------------------
2023-09-08 17:00:00.000     17   0,015
2023-09-14 03:00:00.000      3  10,793   0,0177
2023-09-14 04:00:00.000      4  10,78    0,0176
2023-09-15 16:00:00.000     16   0,197   0,0268
2023-09-15 17:00:00.000     17  10,239   0,0285

解决方案 #

方法1：截断Datetime到小时级别关联 #

将Carrots表的Date字段截断到小时（去除分钟、秒），与Prices表的整点Date匹配，同时结合Hour字段双重验证：

SQL Server 写法

SELECT
    DATEADD(HOUR, DATEPART(HOUR, c.Date), CAST(c.Date AS DATE)) AS Date,
    c.Hour,
    c.Carrot,
    p.Price
FROM
    Carrots c
LEFT JOIN
    Prices p 
    ON DATEADD(HOUR, DATEPART(HOUR, c.Date), CAST(c.Date AS DATE)) = p.Date
    AND c.Hour = p.Hour

MySQL 写法

SELECT
    DATE_FORMAT(c.Date, '%Y-%m-%d %H:00:00') AS Date,
    c.Hour,
    c.Carrot,
    p.Price
FROM
    Carrots c
LEFT JOIN
    Prices p 
    ON DATE_FORMAT(c.Date, '%Y-%m-%d %H:00:00') = p.Date
    AND c.Hour = p.Hour

方法2：通过日期+Hour字段直接关联 #

利用两张表都有的Hour字段，结合日期进行关联：

SELECT
    DATEADD(HOUR, c.Hour, CAST(c.Date AS DATE)) AS Date,
    c.Hour,
    c.Carrot,
    p.Price
FROM
    Carrots c
LEFT JOIN
    Prices p 
    ON CAST(c.Date AS DATE) = CAST(p.Date AS DATE)
    AND c.Hour = p.Hour

说明 #

• 使用LEFT JOIN可保留Carrots表所有数据，即使Prices表无对应日期小时的记录（如示例中2023-09-08 17点的记录）
• 关联条件同时匹配日期+小时，避免了原查询仅匹配日期导致的笛卡尔积问题，确保返回结果准确且无冗余

内容的提问来源于stack exchange，提问作者Kamsiinov