sql - 如何从一个 "table"将多个表连接到同一个自动生成的整数上？

Question

我想创建一个随机选择的交叉连接表，它会自动递增自己的 ID 并在其上连接。假设我的 table 看起来像这样。

人物

Firstname, Lastname 

Hans      | Müller
Joachim   | Bugert

Address
City, Street, StreetNumber
Hamburg | Wandsbeckerstr. | 2
Berlin  | Konradstraße    | 13

现在我想用自动生成的 ID 连接表，它们应该是随机选择的。决赛 table 应该是这样的

ID,Firstname,Lastname, City, Street, StreetNumber
1 |Hans|Bugert|Berlin|Wandsbeckerstr|2
2|Joachim|Müller|Hamburg|Konradstraße | 13

我已经尝试或使用过的:

这里我自动生成我想加入表的ID

select GENERATED_PERIOD_START as ID FROM SERIES_GENERATE_INTEGER(1,1,10)

问题是交叉连接和内部连接对我不起作用，因为它总是将所有内容与所有内容连接起来，或者它不在同一 ID 上连接。

SELECT Person."Firstname", Person."Lastname", Address."City",Address."Street", Address."StreetNumber"

FROM 
(   select GENERATED_PERIOD_START as ID FROM SERIES_GENERATE_INTEGER(1,1,10)

) autoGenID
inner JOIN
(select "Firstname" ,"Lastname" FROM Person ORDER BY RAND()) Person

inner JOIN 
(select "City", "Street", "StreetNumber", FROM Address  ORDER BY RAND()) Address

JOIN ON autoGenID."ID"=?????

这是我的问题，我不能只选择随机数据并在我自动生成的 ID 上选择它。

感谢您提供解决此问题的帮助或想法!

Answer 1

我想你想要:

SELECT p."Firstname", p."Lastname", a."City", a."Street", a."StreetNumber"
FROM (SELECT p.*,
             ROW_NUMBER() OVER (ORDER BY RAND()) as seqnum
      FROM Person p
     ) p JOIN
     (SELECT a.*,
             ROW_NUMBER() OVER (ORDER BY RAND()) as seqnum
      FROM Address a
     ) a
     ON p.seqnum = a.seqnum;