查询以将datetimestamp列与另一个具有startdate和enddate的表连接起来

ivqmmu1c 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(325)

下表有三栏。让我们说 table1 使用以下数据

+---------+-----------------------+----------+
| ID      | E_Date                | TimeDiff |
+---------+-----------------------+----------+
| 7390709 | 2020-03-26 22:25:30   | 456      |
+---------+-----------------------+----------+
| 7390707 | 2020-03-26 16:25:30   | 3        |
+---------+-----------------------+----------+
| 7390706 | 2020-03-26 19:25:30   | 3        |
+---------+-----------------------+----------+
| 7390701 | 2020-03-26 16:23:53   | 512419   |
+---------+-----------------------+----------+
| 7390696 | 2020-03-26 14:33:08   | 111034   |
+---------+-----------------------+----------+
| 7390681 | 2020-03-20   15:25:30 | 2        |
+---------+-----------------------+----------+
| 7390680 | 2020-03-20 16:25:30   | 63867    |
+---------+-----------------------+----------+
| 7390679 | 2020-03-20   13:25:30 | 63867    |
+---------+-----------------------+----------+
| 7390675 | 2020-03-20 12:30:46   | 63867    |
+---------+-----------------------+----------+

假设另一个表有4列 table2 有以下数据

+-----------------------+---------------------+--------+------+
| Start_Date            | End_Date            | Typ_ID | Diff |
+-----------------------+---------------------+--------+------+
| 2020-03-26 18:00:01   | 2020-03-26 21:00:00 | 3      | 180  |
+-----------------------+---------------------+--------+------+
| 2020-03-26   13:40:01 | 2020-03-26 17:30:00 | 1      | 230  |
+-----------------------+---------------------+--------+------+
| 2020-03-26 10:30:01   | 2020-03-26 13:30:00 | 1      | 180  |
+-----------------------+---------------------+--------+------+
| 2020-03-26   06:10:01 | 2020-03-26 10:00:00 | 1      | 230  |
+-----------------------+---------------------+--------+------+
| 2020-03-20 18:00:01   | 2020-03-20 21:00:00 | 1      | 180  |
+-----------------------+---------------------+--------+------+
| 2020-03-20   13:40:01 | 2020-03-20 17:30:00 | 3      | 230  |
+-----------------------+---------------------+--------+------+
| 2020-03-20 10:30:01   | 2020-03-20 13:30:00 | 1      | 180  |
+-----------------------+---------------------+--------+------+
| 2020-03-20   06:10:01 | 2020-03-20 10:00:00 | 1      | 230  |
+-----------------------+---------------------+--------+------+

我想实现的是在表2中检查 start_date 以及 End_Date 哪里 typ_Id = 1 从表1中 E_Date 在表2中 Start_Date 以及 End_Date .
对于id 7390706、73906817390680，e\U日期在表2的开始日期和结束日期范围内，其中类型id=3；对于id 7390709，不存在开始日期和结束日期，因此这些行已从最终结果中排除。
所以最后一张table必须是这样的。

+---------+-----------------------+----------+
| ID      | E_Date                | TimeDiff |
+---------+-----------------------+----------+
| 7390707 | 2020-03-26 16:25:30   | 3        |
+---------+-----------------------+----------+
| 7390701 | 2020-03-26 16:23:53   | 512419   |
+---------+-----------------------+----------+
| 7390696 | 2020-03-26 14:33:08   | 111034   |
+---------+-----------------------+----------+
| 7390679 | 2020-03-20   13:25:30 | 63867    |
+---------+-----------------------+----------+
| 7390675 | 2020-03-20 12:30:46   | 63867    |
+---------+-----------------------+----------+

我仍在学习，所以任何帮助都将不胜感激。
http://sqlfiddle.com/#!18/6eb43年

sql Join sql-server

来源：https://stackoverflow.com/questions/62167033/query-to-join-datetimestamp-column-with-another-table-with-startdate-and-enddate

1条答案

按热度按时间

nnt7mjpx1#

试试下面的演示。

select
    id, 
    e_date,
    timediff
from tableA
join tableB 
on e_date between Start_Date and End_Date
where typ_id = 1

输出：

| id      | e_date                   | timediff |
| ------- | ------------------------ | -------- |
| 7390707 | 2020-03-26 16:25:30      | 3        |
| 7390701 | 2020-03-26 16:23:53      | 512419   |
| 7390696 | 2020-03-26 14:33:08      | 111034   |
| 7390679 | 2020-03-20 13:25:30      | 63867    |
| 7390675 | 2020-03-20 12:30:46      | 63867    |

赞(0）回复(0）举报 2021-07-26

我来回答

查询以将datetimestamp列与另一个具有startdate和enddate的表连接起来

1条答案

相关问题

热门标签

最新问答