如何在不使用JOIN的情况下将2个表中的列合并为1

I am using SQL Server 2008. I have 2 table variables like

我正在使用SQL Server 2008.我有2个表变量，如

FirstName
==========
Little
John  
Baby  

LastName
==========
Timmy
Doe  
Jessica

And I want the result table be:

我希望结果表是：

 First      Last   
=====================
 Little     Timmy    
 John       Doe      
 Baby       Jessica

Note that there is no PK can join the 2 tables. I am trying to use a cursor but not sure how to start.

注意，没有PK可以加入2个表。我试图使用光标，但不知道如何开始。

---- Updated -----

- - 更新 - - -

I know it's a rare case, but I am writing a script to clean up legacy data. The only way we know "Little" goes with "Timmy" is that they are both the first record of the table. Would it help if we had PK for the tables but there is no relation?

我知道这是一种罕见的情况，但我正在编写一个脚本来清理遗留数据。我们知道“小”与“蒂米”的唯一方式是它们都是表中的第一个记录。如果我们有桌子的PK但它没有关系会有帮助吗？

ID FirstName
==========
1  Little
2  John  
3  Baby  
----------

ID LastName
==========
4  Timmy
5  Doe  
6  Jessica
----------

I am not familiar with TSQL so I thought I can loop through the 2 tables like looping through Arrays in memory.

我不熟悉TSQL所以我认为我可以遍历2个表，就像在内存中循环遍历Arrays一样。

5 个解决方案

#1

You could try something like this, to match up based on row numbers:

您可以尝试这样的事情，根据行号进行匹配：

SELECT FirstName AS First, LastName AS Last
FROM
(
  SELECT ROW_NUMBER() OVER (ORDER BY ID) AS RowNum, FirstName
  FROM FirstName
) t1
INNER JOIN
(
  SELECT ROW_NUMBER() OVER (ORDER BY ID) AS RowNum, LastName
  FROM LastName
) t2
ON t1.RowNum = t2.RowNum

But don't take this as a signal that you don't need keys.

但是不要把它当作一个你不需要钥匙的信号。

#2

You can't reliably join these two table variables without identities. Assuming they are being populated in an ordinal manner in the first place, each table could be created with identities as follows:

如果没有标识，则无法可靠地连接这两个表变量。假设它们首先以顺序方式填充，可以使用以下标识创建每个表：

DECLARE @first TABLE(ID INT IDENTITY(1,1), NameFirst VARCHAR(30));
DECLARE @last TABLE(ID INT IDENTITY(1,1), NameLast VARCHAR(30));

-- Note that we don't need to list column names here
INSERT INTO @first VALUES('Little');
INSERT INTO @first VALUES('John');
INSERT INTO @first VALUES('Baby');

INSERT INTO @last VALUES('Timmy');
INSERT INTO @last VALUES('Doe');
INSERT INTO @last VALUES('Jessica');

SELECT n1.NameFirst
, n2.NameLast
FROM @first n1
INNER JOIN @last n2 ON n1.ID=n2.ID;

Result:

结果：

NameFirst                      NameLast
------------------------------ ------------------------------
Little                         Timmy
John                           Doe
Baby                           Jessica

#3

There is no such thing as a "first record". Reading order is totally undefined. This holds true in practice as well! You will see random row order in practice.

没有“第一记录”这样的东西。阅读顺序完全未定义。这在实践中也是如此！您将在实践中看到随机行顺序。

You need to define a sorting order. After doing that you can query like this:

您需要定义排序顺序。完成后，您可以这样查询：

SELECT FirstName AS First, LastName AS Last
FROM
(
  SELECT ROW_NUMBER() OVER (ORDER BY ID) AS r, FirstName
  FROM FirstName
) t1
INNER JOIN
(
  SELECT ROW_NUMBER() OVER (ORDER BY ID) AS r, LastName
  FROM LastName
) t2
ON t1.r = t2.r

Notice the placeholder "SORT ORDER HERE". Need to fill that in. Example: "ID" or "FirstName".

注意占位符“在这里排序”。需要填写它。例如：“ID”或“FirstName”。

Edit: Having seen your edit I added sort order on ID. This will work now.

编辑：看过您的编辑后，我在ID上添加了排序顺序。这将现在奏效。

#4

Here is a hack, that might work in your case:

这是一个hack，可能适合你的情况：

select t1.id, t1.name, t2.name
from (select name, row_number() over (partition by null order by (select NULL)) as id
      from t1
     ) t1 join
     (select name, row_number() over (partition by null order by (select NULL)) as id
      from t2
     ) t2
     on t1.id = t2.id

In my experience, the row_number() with the subquery for the order by means that no sort is actually done, but the rows are brought in sequentially. This is more likely to work if you are using a single threaded instance.

根据我的经验，row_number（）带有订单的子查询，意味着实际上没有进行排序，但是按顺序引入行。如果您使用单线程实例，则更有可能工作。

And, I don't guarantee that it will work! Definitely check the results! If this doesn't work, you might be able to get the same effect by using temporary tables.

并且，我不保证它会起作用！绝对检查结果！如果这不起作用，您可以通过使用临时表获得相同的效果。

Is this data coming from outside the database world? If you have to perform this process more than once, then you should handle the problem either externally or on input into the database.

这些数据是否来自数据库世界之外？如果必须多次执行此过程，则应在外部或输入数据库时处理该问题。

#5

Building on solution @JohnDewey presented, you can sort the records into the table variables. In practice, it will create a relation between the values based on the order of the records:

在解决方案@JohnDewey的基础上，您可以将记录排序到表变量中。在实践中，它将根据记录的顺序在值之间创建关系：

DECLARE @first TABLE(sequence INT IDENTITY(1,1), FirstName VARCHAR(30));
DECLARE @last TABLE(sequence INT IDENTITY(1,1), LastName VARCHAR(30));

INSERT INTO @first(FirstName)
SELECT FirstName FROM TableFisrt ORDER BY id;
-- sequence FirstName
-- ======== =========
--        1  Little
--        2  John  
--        3  Baby 

INSERT INTO @last(LastName)
SELECT FirstName FROM TableLast ORDER BY id;
-- sequence LastName
-- ======== =========
--        1  Timmy
--        2  Doe
--        3  Jessica 

SELECT frs.FirstName, lst.LastName
FROM @first frs
INNER JOIN @last lst ON frs.sequence = lst.sequence;
-- sequence FirstName
-- ======== =========
-- Little   Timmy
-- John     Doe
-- Baby     Jessica

#1