代码之家 › 专栏 › 技术社区 › Freakishly

如何连接两个表并仅从第二个表(如果存在)中获取值?

select join tsql sql-server sql

1

Freakishly · 技术社区 · 7 年前

表1只是一列&28排。每行是过去28天的日期时间。

表2有两列:DateTime和Value。行数不可预测,但DateTime始终在最后28天内。

我想连接这两个表,这样生成的表就有两列,DateTime和Value,以及28行,在过去28天中每列一行。值列取自表2(如果存在),或NULL或-1(如果不存在)。

最好的方法是什么?

表1 (28x1)

DATE
11/3/2018 12:00:00AM
11/4/2018 12:00:00AM
11/5/2018 12:00:00AM
...
...
11/30/2018 12:00:00AM

表2 (4x2)

DATE                  | VALUE
----------------------+-------
11/3/2018 12:00:00AM  |  45
11/21/2018 12:00:00AM |  78
11/22/2018 12:00:00AM |  85
11/29/2018 12:00:00AM | 103

预期结果(28x2)

DATE                 | VALUE
---------------------+-------
11/3/2018 12:00:00AM | 45
11/4/2018 12:00:00AM | null
11/5/2018 12:00:00AM | null
...
...
11/20/2018 12:00:00AM | null
11/21/2018 12:00:00AM | 78
11/22/2018 12:00:00AM | 85
11/23/2018 12:00:00AM | null
...
11/29/2018 12:00:00AM | 103
11/30/2018 12:00:00AM | null

到目前为止,我尝试的任何连接都不起作用,因为我必须在一个列上进行连接,并且唯一的公共列是DateTime&这将消除没有值的行。

3 回复 | 直到 7 年前

1

Mureinik 7 年前

这是什么 left join

SELECT    t1.[datetime], t2.[value]
FROM      t1
LEFT JOIN t2 ON t1.[datetime] = t2.[datetime]

2

Ajay2707 calm 7 年前

我对列名的建议是,不要在Date或Datetime等列中使用sqlserver的保留字。

下面给出的例子将解决您的问题:(正如上面建议的答案,您需要左加入)

declare @table1 table( dateColumn datetime)
declare @table2 table( dateColumn datetime, value int)

--insert into @table1 values ('11/3/2018 12:00:00AM' ),( '11/4/2018 12:00:00AM'), ('11/5/2018 12:00:00AM')
--To test I insert current month's all dates in @table1
Declare @year int = 2018, @month int = 11;
WITH numbers
as
(
    Select 1 as value
    UNion ALL
    Select value + 1 from numbers
    where value + 1 <= Day(EOMONTH(datefromparts(@year,@month,1)))
)

Insert into @Table1  
SELECT datefromparts(@year,@month,numbers.value) Datum FROM numbers

insert into @table2 values ('11/3/2018 12:00:00AM' , 45), ('11/21/2018 12:00:00AM', 78), ('11/22/2018 12:00:00AM', 85), ('11/29/2018 12:00:00AM', 103)

SELECT    t1.dateColumn, t2.[value]
FROM    @table1  t1
LEFT JOIN @table2 t2 ON t1.dateColumn = t2.dateColumn

3

0

Moinul Islam 7 年前

SELECT    t1.[datetime], t2.[value]
FROM      t1
LEFT JOIN t2 ON cast(t1.[datetime] as date) = cast(t2.[datetime] as date)