sql - 如何联合 SELECT 两个具有两个表的 ID 的表？

Question

好的，我有四个表:

表 1:“f_withholdings”

表 2:“f_wh_list”

表 3:“f_rpayments”

表 4:“f_rp_list”

表1和表2通过wh_id字段相互连接如图所示，表 3 和表 4 通过 rp_id 连接。

我想将两个表合并为一个表，例如:

SELECT
`wh_list_id`,
`wh_name` AS `name`,
`wh_list_date` AS `date`,
`wh_list_amount` AS `amount`,
`wh_list_pending` AS `pending`,
`wh_list_comment` AS `comment`
FROM
`f_wh_list` LEFT JOIN `f_withholdings` ON `f_wh_list`.`wh_id` = `f_withholdings`.`wh_id`

UNION ALL

SELECT
`rp_list_id`,
`rp_name` AS `name`,
`rp_list_date` AS `date`,
`rp_list_amount` AS `amount`,
`rp_list_pending` AS `pending`,
`rp_list_comment` AS `comment`
FROM `f_rp_list` LEFT JOIN `f_rpayments` ON `f_rp_list`.`rp_id` = `f_rpayments`.`rp_id`

我明白了:

结果表中只有第一个 SELECT wh_list_id 的 id 字段，但没有 rp_list_id

我想在结果表中包含两个 ID，如下所示:

谢谢!

Answer 1

只需选择 null 作为每个缺失的列。

SELECT
`wh_list_id`,
null AS `rp_list_id`,
`wh_name` AS `name`,
`wh_list_date` AS `date`,
`wh_list_amount` AS `amount`,
`wh_list_pending` AS `pending`,
`wh_list_comment` AS `comment`
FROM
`f_wh_list` LEFT JOIN `f_withholdings` ON `f_wh_list`.`wh_id` = `f_withholdings`.`wh_id`

UNION ALL

SELECT
null as `wh_list_id`,
`rp_list_id`,
`rp_name` AS `name`,
`rp_list_date` AS `date`,
`rp_list_amount` AS `amount`,
`rp_list_pending` AS `pending`,
`rp_list_comment` AS `comment`
FROM `f_rp_list` LEFT JOIN `f_rpayments` ON `f_rp_list`.`rp_id` = `f_rpayments`.`rp_id`