sql - 在一个表中显示两个临时表数据

Question

我有一个临时表，在 sql server 中有如下数据

表格1

**Data** 
ISD-I987330
PSD-I987330
KSD-I987330
JSD-I987330
RSD-I987330
QSD-I987330
QSD-I987359

另一个临时表具有如下数据

表 2

**Data**
BRA-22310
BRA-22319
BRA-22316
BRA-22313
BRA-22317

我正在尝试在一个表中显示这两个表数据。如下所示

但我得到了交叉连接数据。

以下是我的查询

declare @TempResults table
(


    Tickets1    varchar(50),  
    Tickets2    varchar(50)
)

insert into @TempResults 
Select distinct ti.Tickets1,
tr.Tickets2
FROM @Table1 ti,@table2 tr

select * from  @TempResults 

标签： sqlsql-server

Answer 1

You can't really get the output you want without each table having a separate column which maintains the order of each record, in each table. Absent that, we could use ROW_NUMBER to generate an order, and then join:

WITH cte1 AS (
    SELECT Data, ROW_NUMBER() OVER (ORDER BY Data) rn
    FROM Table1
),
cte2 AS (
    SELECT Data, ROW_NUMBER() OVER (ORDER BY Data) rn
    FROM Table2
)

SELECT
    t1.Data,
    t2.Data
FROM cte1 t1
FULL OUTER JOIN cte2 t2
    ON t1.rn = t2.rn;