我有两个表需要根据DateTime列连接,但在另一个表上,我的DateTime也包括秒.我的表及其数据类型如下:
表格:Carrots
份
列:
Date (datetime) -> with example value "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) -> with example value "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
如何仅使用DATETIME列的一部分将这两个列连接在一起?
到目前为止,我已经try 了一些我从中找到的东西,但我得到了太多的结果.我在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