组合两个表而不会丢失列或行

I have two tables:

我有两张桌子:

The first one has the colums "SomeValue" and "Timestamp". The other one has the columns "SomeOtherValue" and also "Timestamp".

第一个是colums“SomeValue”和“Timestamp”。另一个列有“SomeOtherValue”列和“Timestamp”列。

What I need as an output is the following:

我需要的输出如下:

A table with the three Colums "SomeValue", "SomeOtherValue" and "Timestamp".

一个包含三个Colums“SomeValue”,“SomeOtherValue”和“Timestamp”的表。

When a row in table 1 is like this: [2; 04/07/2017-20:05] and a row in table 2 is like that: [5; 04/07/2017-20:05], I want the combined output row to be [2; 5; 04/07/2017-20:05].

当表1中的一行是这样的:[2; 04/07 / 2017-20:05]并且表2中的一行是这样的:[5; 04/07 / 2017-20:05],我希望组合输出行为[2; 5; 4月7日/ 2017-20:05]。

Until that point it would be easy done with a simple join, but I also need all other rows. So for example if we have a row in table 1 like [2; 04/07/2017-20:05] and no matching timestamp in table 2, the output should be like [2; ?; 04/07/2017-20:05]. The '?' stands for undefined or null. It would also be possible to not join two rows with the same timestamp but rather concating both tables, so that every row would have one empty cell with '?'.

在那之前,通过简单的连接可以轻松完成,但我还需要所有其他行。例如,如果我们在表1中有一行像[2; 04/07 / 2017-20:05]并且表2中没有匹配的时间戳,输出应该像[2; ?; 4月7日/ 2017-20:05]。 '?'代表undefined或null。也可以不使用相同的时间戳连接两行,而是将两个表连接起来,这样每行都会有一个带“?”的空单元格。

I do realize that I didn't use correct Date/Time Format here in that example, but assume that it is used in the database.

我确实在这个例子中没有使用正确的日期/时间格式,但是假设它在数据库中使用。

I already tried using UNION ALL but it always removes one column. For my use case it is not possible to query both tables independently. I really need both values in one row/object.

我已经尝试过使用UNION ALL,但它总是删除一列。对于我的用例,无法独立查询两个表。我真的需要一行/对象中的两个值。

I hope someone can help me with this. Thank you!

我希望有人可以帮助我。谢谢!

2 个解决方案

#1

What you are describing is a full outer join:

你所描述的是一个完整的外部联接:

select t1.somevalue, t2.someothervalue, timestamp
from t1 
full outer join t2 using (timestamp);

I don't know, however, whether SAP HANA supports the USING clause. Here is the same query with ON instead:

但是,我不知道SAP HANA是否支持USING子句。以下是与ON相同的查询:

select 
  t1.somevalue, 
  t2.someothervalue, 
  coalesce(t1.timestamp, t2.timestamp) as timestamp
from t1 
full outer join t2 on t2.timestamp = t1.timestamp;

#2

Joining on a datetime stamp is not always going to be reliable unless you are setting a datetime variable and writing the value of that to both tables. It's probably not very efficient either.
That said, assuming you want all the results from table 1 and matching table 2 result if it exists then you need a left outer join

除非您设置datetime变量并将其值写入两个表,否则加入日期时间戳并不总是可靠的。它可能也不是很有效率。也就是说,假设您希望表1中的所有结果和匹配表2的结果存在,那么您需要左外连接

Select T1.[SomeValue]
, ISNULL(T2.[SomeOtherValue], '?')
, T1.[TimeStamp]
FROM Table1 T1
LEFT OUTER JOIN Table2 T2
ON T2.[TimeStamp] = T1.[TimeStamp]

Update based on comment from OP If you need all rows from both tables then you could either do 2 queries as above but interchange T1 and T2 position then union the 2 queries.

基于来自OP的注释进行更新如果您需要来自两个表的所有行,那么您可以执行上述2个查询,但是交换T1和T2位置然后将2个查询联合起来。

SELECT T1.[TimeStamp]
, T1.[SomeValue]
, ISNULL(T2.[SomeOtherValue], '?')
FROM Table1 T1
LEFT OUTER JOIN Table2 T2
ON T2.[TimeStamp] = T2.[TimeStamp]
UNION
SELECT T2.[TimeStamp]
, T2.[SomeValue]
, ISNULL(T1.[SomeOtherValue], '?')
FROM Table2 T2
LEFT OUTER JOIN Table1 T1
ON T1.[TimeStamp] = T2.[TimeStamp]
;

Or you could insert the 1st query results into a table variable then add any missing from T2 rows into that table variable using a where not exists, then select the output.

或者,您可以将第一个查询结果插入表变量,然后使用不存在的地方将T2行中的任何缺失添加到该表变量中,然后选择输出。

DECLARE @TempTab TABLE
( [TimeStamp] [datetime] NOT NULL
, [SomeValue] [nvarchar] (MAX) -- or int if this is always an integer
, [SomeOtherValue] [nvarchar] (MAX) -- or int if this is always an integer
)
;

INSERT INTO @TempTab
( [TimeStamp]
, [SomeValue]
, [SomeOtherValue]
)
SELECT T1.[TimeStamp]
, T1.[SomeValue]
, ISNULL(T2.[SomeOtherValue], '?')
FROM Table1 T1
LEFT OUTER JOIN Table2 T2
ON T2.[TimeStamp] = T2.[TimeStamp]
;

INSERT INTO @TempTab
( [TimeStamp]
, [SomeValue]
, [SomeOtherValue]
)
SELECT T2.[TimeStamp]
, T2.[SomeValue]
, ISNULL(T1.[SomeOtherValue], '?')
FROM Table2 T2
LEFT OUTER JOIN Table1 T1
ON T1.[TimeStamp] = T2.[TimeStamp]
WHERE NOT EXISTS
(   SELECT 1
    FROM @TempTab T
    WHERE T.[TimeStamp] = T2.[TimeStamp]
)
;

SELECT T.[TimeStamp]
, T.[SomeValue]
, T.[SomeOtherValue]
FROM @TempTab T
;

#1