sql - 加入具有相同日期列的日期范围
问题描述
再会。我在数据库中有两个表。我想加入他们,但我似乎检索的行数超出了需要。除了日期范围之外,这两个表都没有任何关系。
如果表 b 的日期在表 a 的日期范围内;然后加入。但是,我似乎加入了很多比需要的方式。
如何附加表 B 中与表 A 最接近的日期范围的用户名?非常感谢任何帮助。
表 A
dateS auditmessage
2018-04-09 07:06:48.290 Phase 2) (Heat Up) Parameter 3) changed from 8.0 to 5.0
2018-04-09 07:06:48.290 Phase 2) (Heat Up) Parameter 24) changed from 15.0 to 14.5
2018-04-09 07:06:48.290 Phase 3) (Sterilization) Parameter 24) changed from 15.0 to 14.5
2018-04-09 07:06:48.290 Phase 4) (Air Pressurization) Parameter 11) changed from 15.0 to 14.5
2018-04-09 07:06:51.380 Phase 2) (Heat Up) Parameter 3) changed from 8.0 to 5.0
2018-04-09 07:06:51.380 Phase 2) (Heat Up) Parameter 24) changed from 15.0 to 14.5
2018-04-09 07:06:51.380 Phase 3) (Sterilization) Parameter 24) changed from 15.0 to 14.5
2018-04-09 07:06:51.380 Phase 4) (Air Pressurization) Parameter 11) changed from 15.0 to 14.5
2018-04-09 07:08:31.737 Phase 1) (Setup) Parameter 18) changed from 6.0 to 7.0
2018-04-09 07:08:31.737 Phase 4) (Air Pressurization) Parameter 20) changed from 0 to 1
2018-04-09 07:08:34.977 Phase 1) (Setup) Parameter 18) changed from 6.0 to 7.0
2018-04-09 07:08:34.977 Phase 4) (Air Pressurization) Parameter 20) changed from 0 to 1
2018-04-09 09:25:15.967 Phase 1) (Setup) Parameter 1) changed from 0 to 2
2018-04-09 09:25:15.967 Phase 4) (Air Pressurization) Parameter 11) changed from 14.5 to 17.5
2018-04-09 09:25:15.967 Phase 1) (Setup) Parameter 18) changed from 6.0 to 5.0
2018-04-09 09:25:15.967 Phase 1) (Setup) Parameter 3) changed from 2 to 0
2018-04-09 09:25:15.967 Phase 1) (Setup) Parameter 18) changed from 7.0 to 6.0
2018-04-09 09:25:15.967 Phase 2) (Heat Up) Parameter 24) changed from 14.5 to 17.5
2018-04-09 09:25:19.247 Phase 1) (Setup) Parameter 1) changed from 0 to 2
2018-04-09 09:25:19.247 Phase 4) (Air Pressurization) Parameter 11) changed from 14.5 to 17.5
2018-04-09 09:25:19.247 Phase 1) (Setup) Parameter 18) changed from 6.0 to 5.0
2018-04-09 09:25:19.247 Phase 1) (Setup) Parameter 3) changed from 2 to 0
2018-04-09 09:25:19.247 Phase 1) (Setup) Parameter 18) changed from 7.0 to 6.0
2018-04-09 09:25:19.247 Phase 2) (Heat Up) Parameter 24) changed from 14.5 to 17.5
2018-04-09 12:32:30.123 Phase 1) (Setup) Parameter 3) changed from 0 to 2
2018-04-09 12:32:32.253 Phase 1) (Setup) Parameter 3) changed from 0 to 2
表 B
userName DateS
sam 2018-04-09 07:11:45.907
andy 2018-04-09 09:16:41.023
carl 2018-04-09 12:17:45.057
我的失败查询
SELECT
k.dates,
k.auditmessage,
l.username
FROM tableA k
INNER JOIN tableB l
-- on l.dateS >=k.dateS --and l.dateS <= k.dateS
--where l.dateS >=k.dateS and l.dateS<=k.dateS
期望的结果
dateS auditmessage User
2018-04-09 07:06:48.290 Phase 2) (Heat Up) Parameter 3) changed from 8.0 to 5.0 sam
2018-04-09 07:06:48.290 Phase 2) (Heat Up) Parameter 24) changed from 15.0 to 14.5 sam
2018-04-09 07:06:48.290 Phase 3) (Sterilization) Parameter 24) changed from 15.0 to 14.5 sam
2018-04-09 07:06:48.290 Phase 4) (Air Pressurization) Parameter 11) changed from 15.0 to 14.5 sam
2018-04-09 07:06:51.380 Phase 2) (Heat Up) Parameter 3) changed from 8.0 to 5.0 sam
2018-04-09 07:06:51.380 Phase 2) (Heat Up) Parameter 24) changed from 15.0 to 14.5 sam
2018-04-09 07:06:51.380 Phase 3) (Sterilization) Parameter 24) changed from 15.0 to 14.5 sam
2018-04-09 07:06:51.380 Phase 4) (Air Pressurization) Parameter 11) changed from 15.0 to 14.5 sam
2018-04-09 07:08:31.737 Phase 1) (Setup) Parameter 18) changed from 6.0 to 7.0 sam
2018-04-09 07:08:31.737 Phase 4) (Air Pressurization) Parameter 20) changed from 0 to 1 sam
2018-04-09 07:08:34.977 Phase 1) (Setup) Parameter 18) changed from 6.0 to 7.0 sam
2018-04-09 07:08:34.977 Phase 4) (Air Pressurization) Parameter 20) changed from 0 to 1 sam
2018-04-09 09:25:15.967 Phase 1) (Setup) Parameter 1) changed from 0 to 2 andy
2018-04-09 09:25:15.967 Phase 4) (Air Pressurization) Parameter 11) changed from 14.5 to 17.5 andy
2018-04-09 09:25:15.967 Phase 1) (Setup) Parameter 18) changed from 6.0 to 5.0 andy
2018-04-09 09:25:15.967 Phase 1) (Setup) Parameter 3) changed from 2 to 0 andy
2018-04-09 09:25:15.967 Phase 1) (Setup) Parameter 18) changed from 7.0 to 6.0 andy
2018-04-09 09:25:15.967 Phase 2) (Heat Up) Parameter 24) changed from 14.5 to 17.5 andy
2018-04-09 09:25:19.247 Phase 1) (Setup) Parameter 1) changed from 0 to 2 andy
2018-04-09 09:25:19.247 Phase 4) (Air Pressurization) Parameter 11) changed from 14.5 to 17.5 andy
2018-04-09 09:25:19.247 Phase 1) (Setup) Parameter 18) changed from 6.0 to 5.0 andy
2018-04-09 09:25:19.247 Phase 1) (Setup) Parameter 3) changed from 2 to 0 andy
2018-04-09 09:25:19.247 Phase 1) (Setup) Parameter 18) changed from 7.0 to 6.0 andy
2018-04-09 09:25:19.247 Phase 2) (Heat Up) Parameter 24) changed from 14.5 to 17.5 andy
2018-04-09 12:32:30.123 Phase 1) (Setup) Parameter 3) changed from 0 to 2 carl
2018-04-09 12:32:32.253 Phase 1) (Setup) Parameter 3) changed from 0 to 2 carl
解决方案
您可以使用 搜索最近的行outer apply
。对于 TableB 中的每一行,它根据距离对 TableA 行进行排序,然后选择第一行:
select *
from TableB b
outer apply
(
select top 1 *
from TableA a
order by
abs(b.dateS - a.dateS)
) a
推荐阅读
- swift - 如何子类化 AVCaptureInput 和 AVCaptureInput.Port?
- java - Ant 没有使用我的 JAVA_HOME 来查找 tools.jar
- java - Java - 如何在 if 语句中添加 rgb 值?
- c# - 是否有 api 接口可以与 Unreal 进行材质交换?
- unity3d - 阻止粒子与相机一起滚动(统一)
- sql-server - TSQL 包含有条件的帐户
- java - 在这种情况下,为什么我会收到错误“Bean name 'user' 的 BindingResult 和普通目标对象都不能用作请求属性”?
- c++ - 制作可用于初始化向量的迭代器
- javascript - 对象作为 React 子项无效 - 文本输入
- performance - 数据流不同的操作不缩放