在结果集中并列连接列 [英] Join columns side by side in result set

问题描述

我希望我的结果看起来像这样

I would like my result to look something like this

    FirstName1     LastName1         FirstName2      LastName2
       Amy           Smith              Bob            Stone
      Fred           Joker              Gina           White

FirstName1和FirstName2具有相同的数据类型，但是没有任何我可以用来联接的数据(假设没有人具有相同的名称)，LastName1和LastName2也是一样.
我试图创建2个表.第一个表包含FirstName1和LastName1.第二个表包含Firstname2和LastName2.
然后我用

Where FirstName1 and FirstName2 have same data types but nothing I can use to join (assume no one has same names) and the same goes for LastName1 and LastName2.
I tried to create 2 tables. First table contains FirstName1 and LastName1. Second table contains Firstname2 and LastName2.
Then I use

SELECT table1.FirstName1, table1.LastName1, table2.FirstName2, table2.LastName2
FROM table1, table2;

但这给了我很多重复.有什么建议?

But this gives me a lot of duplicates. Any suggestions?

推荐答案

SELECT t1.FirstName1, t1.LastName1, t2.FirstName2, t2.LastName2
FROM
    (SELECT 
         FirstName1, 
         LastName1, 
         ROW_NUMBER() OVER (ORDER BY FirstName1) 'RowNumber'
     FROM table1
    ) AS t1
    FULL OUTER JOIN
    (SELECT 
         FirstName2, 
         LastName2, 
         ROW_NUMBER() OVER (ORDER BY FirstName2) 'RowNumber'
     FROM table2
    ) AS t2
    ON t1.RowNumber = t2.RowNumber

FULL OUTER JOIN将处理两个表中的行数不同的情况.

FULL OUTER JOIN will handle the cases where the number of rows from the two tables are not the same.

这篇关于在结果集中并列连接列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！