mysql - MySQL 不相关的列或使用行号连接表

Question

我有这些桌子

tbl_village

id | village_no 
1 | V1
2 | V2
3 | V3

tbl_house

id | house_no |village_id 
1 | H1 | 1
2 | H2 | 1
3 | H3 | 2

tbl_street

id | street_no | village_id 
1 | S1 | 1 
2 | S2 | 1 
3 | S3 | 1 

tbl_house 和 tbl_street 没有关系。

现在我想创建一个脚本，结果如​​下：

Village No | House Nos | Streets
V1 | H1 | S1
V1 | H2 | S2 
V1 | __ | S3 
V2 | H3 | __
V3 | __ | __

我试过这段代码：

SELECT v.village_no, h.house_no, s.street_no
FROM tbl_village v LEFT OUTER JOIN tbl_house h ON (v.id=h.village_id) 
   LEFT OUTER JOIN tbl_street s ON (v.id=s.village_id)
GROUP BY v.village_no, h.house_no, s.street_no

脚本结果：

Village No | House Nos | Streets
V1 | H1 | S1
V1 | H2 | S1
V1 | H1 | S2
V1 | H2 | S2
V1 | H1 | S3
V1 | H2 | S3
V2 | H3 | __
V3 | __ | __    

它没有给出我想要的结果，因为 tbl_house 和 tbl_street 彼此不相关。

标签： mysql

Answer 1

在 MySQL 8 中，您可以通过ROW_NUMBER()在模拟中使用连接每个村庄的房屋和街道值FULL JOIN（通过使用 aUNION和LEFT连接RIGHT）来实现这一点，然后LEFT JOIN将其连接到村表以获取每个村庄的房屋和街道：

WITH houses AS (
  SELECT house_no, village_id,
         ROW_NUMBER() OVER (PARTITION BY village_id ORDER BY house_no) AS rn
  FROM tbl_house
),
streets AS (
  SELECT street_no, village_id,
         ROW_NUMBER() OVER (PARTITION BY village_id ORDER BY street_no) AS rn
  FROM tbl_street
),
sh AS (
  SELECT h.village_id, h.house_no, s.street_no, h.rn
  FROM houses h 
  LEFT JOIN streets s ON s.village_id = h.village_id AND s.rn = h.rn
  UNION
  SELECT s.village_id, h.house_no, s.street_no, s.rn
  FROM houses h 
  RIGHT JOIN streets s ON s.village_id = h.village_id AND s.rn = h.rn
)
SELECT v.village_no, sh.house_no, sh.street_no
FROM tbl_village v
LEFT JOIN sh ON sh.village_id = v.id
ORDER BY v.id, sh.rn

输出：

village_no  house_no    street_no
V1          H1          S1
V1          H2          S2
V1                      S3
V2          H3  
V3      

dbfiddle 上的演示