从两个没有连接条件的表t-sql中选择数据

I'd appreciate if someone could help. I have two tables that have no relationship:

如果有人能帮忙我将不胜感激。我有两张桌子没有关系:

Table_1

ID    NAME    VALUE
1     abc     10
2     def     20
3     def     20

Table_2

   ID2    NAME2    VALUE2
    5     ghi     30
    6     gkl     40

I want to have a select statement that would show the data from both tables like this:

我想要一个select语句来显示来自这两个表的数据:

   ID    NAME    VALUE  ID2   NAME2   VALUE2
    1     abc     10    5     ghi     30
    2     def     20    6     gkl     40
    3     def     20

The point is to show data of each record in one row, the table can look like:

重点是在一行中显示每条记录的数据，表可以是:

 ID    NAME    VALUE  ID2   NAME2   VALUE2
                      5     ghi     30
                      6     gkl     40

If Table_1 has no records. Same is true for Table_2. I tried to use cross join, but then the data will repeat.

如果表_1没有记录。表_2也是如此。我尝试使用交叉连接，但是数据会重复。

Thanks a lot

非常感谢

3 个解决方案

#1

You need to add a join condition. In this case, by using row_number() to add a sequential number on each side. Then full outer join to get all the records:

您需要添加一个连接条件。在本例中，通过使用row_number()在每一边添加一个序号。然后全外连接获取所有记录:

select t1.id, t1.name, t1.value, t2.id as id2, t2.name as name2, t2.value as value2
from (select t1.*, row_number() over (order by id) as seqnum
      from table_1 t1
     ) t1 full outer join
     (select t2.*, row_number() over (order by id) as seqnum
      from table_2 t2
     ) t2
     on t1.seqnum = t2.seqnum;

#2

Try this:

试试这个:

with Table_1(ID, NAME, VALUE) as (
  select 1, 'abc', 10 union all
  select 2, 'def', 20 union all
  select 3, 'def', 20
), Table_2(ID2, NAME2, VALUE2) as (
  select 5, 'ghi', 30 union all
  select 6, 'gkl', 40
), prep_table_1 (ID, NAME, VALUE, rn) as (
  select id, name, value, row_number() over(order by id)
    from table_1
), prep_table_2 (ID2, NAME2, VALUE2, rn) as (
  select id2, name2, value2, row_number() over(order by id2)
    from table_2
)
select t1.ID, t1.NAME, t1.VALUE, t2.ID2, t2.NAME2, t2.VALUE2
  from prep_table_1 t1
  full outer join prep_table_2 t2 on t1.rn = t2.rn

SQLFiddle

#3

This also works

这同样适用

select * from Table_1,Table_2

select * from Table_1 Table_2

#1