如何编写SQL查询两次引用同一张State表获取双状态信息？

你需要的应该是把房产所在州和其对应分行所在州同时展示在结果里，核心问题在于得给State表的两个实例设置不同的别名，以此区分关联的是房产地址还是分行地址，同时还要建立房产到分行的关联链路（你的第一个查询里包含staff_no，刚好可以通过Staff表完成这个关联）。

问题分析 #

• 之前用Union失败是因为两个查询的列数、列类型不匹配，Union要求合并的结果集必须结构完全一致；
• 使用Full Join没成功，大概率是没给State表的两个副本设置不同别名，导致SQL无法区分这两个相同的表引用。

解决方案 #

我们给State表分别设置s（对应房产地址的州）和z（对应分行地址的州）两个别名，同时通过Staff表把Property_for_rent和Branch关联起来，就能得到包含property_state和branch_state的完整房产数据：

SELECT 
    p.property_no, 
    p.prop_type, 
    p.rooms, 
    p.rent, 
    s.name AS property_state, 
    p.staff_no,
    b.branch_no,
    z.name AS branch_state
FROM property_for_rent p
-- 关联房产地址和对应的州
JOIN address a_p ON p.address_id = a_p.address_id
JOIN state s ON a_p.state_id = s.state_id
-- 通过员工关联所属分行，用LEFT JOIN避免丢失无员工的房产数据
LEFT JOIN staff st ON p.staff_no = st.staff_no
-- 关联分行地址和对应的州
LEFT JOIN branch b ON st.branch_id = b.branch_id
LEFT JOIN address a_b ON b.address_id = a_b.address_id
LEFT JOIN state z ON a_b.state_id = z.state_id
ORDER BY p.rent ASC;

关键说明 #

• 用LEFT JOIN替代JOIN是为了保留像PR806这种没有对应员工/分行的房产数据；
• 给两个State表实例取不同别名后，SQL能清晰区分哪个是关联房产地址的州，哪个是关联分行地址的州；
• 完整关联链路：房产 -> 房产地址 -> 房产州 + 房产 -> 员工 -> 分行 -> 分行地址 -> 分行州。

预期结果示例 #

结合你给出的两个查询结果，最终输出会类似：

+-------------+-----------+-------+------+----------------+----------+-----------+----------------+
| property_no | prop_type | rooms | rent | property_state | staff_no | branch_no | branch_state   |
+-------------+-----------+-------+------+----------------+----------+-----------+----------------+
| PR200       | Flat      | 3     | 24   | Nevada         | SQ523    | BXXX      | [对应分行的州] |
| PR901       | Flat      | 7     | 31   | Vermont        | SL569    | BXXX      | [对应分行的州] |
| PR806       | House     | 3     | 54   | Minnesota      | NULL     | NULL      | NULL           |
+-------------+-----------+-------+------+----------------+----------+-----------+----------------+

内容的提问来源于stack exchange，提问作者Esteban Ordoñez