T-SQL内部连接查询 [英] T-SQL Inner Join Query

问题描述

我有两张桌子，我想参考之间;一个表包含2000多行ID和日期时间，第二个表只包含400 - / +日期时间。



我需要在表中找到所有日期时间（dt_tbl1） 1表示与表2中相同的时间（表2是我的参考点），从表1中生成一个ID表，该表列在表2的日期时间（dt_tbl2）附近。



基本上找到表1中的400 - / + ID，表1也比表2晚2 - 10秒。



列输入IS日期时间，而不是浮点数，varchar或类似等。



希望你到目前为止跟着我。



这是查询;

Hi,

I have two tables that I'm looking to reference between; one table holds 2000+ rows of datetimes with ID's and the second table holds just 400 -/+ datetimes.

I need to find all the datetime (dt_tbl1) in table 1 that are round about the same time as those in table 2, (table 2 is my reference point) producing a list of ID's from table 1 that are around the datetime (dt_tbl2) of table 2.

Basically finding the 400 -/+ ID's in table 1, also table 1 is 2 - 10 seconds behind table 2.

The column type IS datetime, not float, varchar or alike etc.

Hope you follow me so far.

Here's the query ;

SELECT * FROM @table2 as tbl2
inner join @table1 as tbl1 on tbl1.dt_tbl1 = tbl2.dt_tbl2

但我知道

However I know the

tbl1.dt_tbl1 = tbl2.dt_tbl2

部分只会查找完全匹配，但我需要它来查找日期时间（dt_tblx）给出或需要2-10秒。



任何人都知道如何详细说明这个问题吗？



我猜它会像

part will just look for exact matches, but I need it to find datestimes (dt_tblx) give or take 2 - 10 seconds.

Anyone know how I can elaborate the query to do this?

I'm guessing it would be something like

DATEDIFF(SS,tbl2.dt_tbl2,tbl1_dt_tbl1) <= 4

谢谢，

詹姆斯

Thanks,
James

推荐答案

看看 BETWEEN 命令

Look at the BETWEEN command

t1.date BETWEEN DATEADD(second, 2, t2.date) AND DATEADD(second, 10, t2.date)

或者你可以写多个逻辑声明

Or you can write multiple logic statement

t1.date > DATEADD(second, 2, t2.date) AND t1.date < DATEADD(second, 10, t2.date)

你也可以找到其他一些方法。你必须尝试它们，看看哪个是禁食的。



以下文章讨论了这些以及更多：



http://social.msdn.microsoft.com/Forums/sqlserver/en-US/33d56faf-7eed-455e-8d75-93d22291ae9a/allow-for-1-second-variance-in-datetime-join [ ^ ]

这篇关于T-SQL内部连接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！