MySQL - 如何在左连接中给整个表起别名

Question

我有这样一个情况，一个属性表拥有来自g_addresses表的地址id，而申请人表同样也拥有来自g_addresses的地址id。 我想将它们左连接在一起，但选择表中的所有字段。

我知道使用“as”为字段创建别名的方法，但是否有任何方法为整个表创建别名？

SELECT *
FROM (`reference`)
LEFT JOIN `applicants` ON `applicants`.`id` = `reference`.`applicant_id`
LEFT JOIN `g_people` applicant_person ON `applicant_person`.`id` = `applicants`.`person_id`
LEFT JOIN `g_addresses` applicant_address ON `applicant_address`.`id` = `applicants`.`address_id`
LEFT JOIN `properties` ON `properties`.`id` = `reference`.`property_id`
LEFT JOIN `g_addresses` property_address ON `property_address`.`id` = `properties`.`address_id`
WHERE `reference`.`id` = 4 

这会产生一个只包含一个地址行而不是两个的结果， 返回的行是最后一个连接的行而不是之前的行，表明它在返回时进行了覆盖。

- Anthony

Answer 1

我认为在你的情况下不应该使用带有掩码的引用，比如*或者`reference`.*，因为你可能会得到包含相同列名（id，address_id）的结果集。

如果你想从关联的表中获取所有列，你应该在SELECT子句中逐个指定它们，并为每个列分配唯一别名：

SELECT
  ref.`id` AS ref_id,
  ref.`…`  AS …,
  …
  app.`id` AS app_id,
  …
FROM `reference` AS ref
LEFT JOIN `applicants`  AS app ON app.`id` = ref.`applicant_id`
LEFT JOIN `g_people`    AS ape ON ape.`id` = app.`person_id`
LEFT JOIN `g_addresses` AS apa ON apa.`id` = app.`address_id`
LEFT JOIN `properties`  AS pro ON pro.`id` = ref.`property_id`
LEFT JOIN `g_addresses` AS pra ON pra.`id` = pro.`address_id`
WHERE ref.`id` = 4

- Andriy M

---

谢谢，我用这个作为我的最终解决方案！ - Anthony

Answer 2

请更具体地选择您要查询的列

SELECT 
  applicant_address.*,
  property_address.*,
  applicants.*,
  applicant_person.*,
  properties.*
FROM (`reference`)
LEFT JOIN `applicants` ON `applicants`.`id` = `reference`.`applicant_id`
LEFT JOIN `g_people` applicant_person ON `applicant_person`.`id` = `applicants`.`person_id`
LEFT JOIN `g_addresses` applicant_address ON `applicant_address`.`id` = `applicants`.`address_id`
LEFT JOIN `properties` ON `properties`.`id` = `reference`.`property_id`
LEFT JOIN `g_addresses` property_address ON `property_address`.`id` = `properties`.`address_id`
WHERE `reference`.`id` = 4 

- Michael Berkowski

---

我想从每个表中选择每一列 - 这听起来可能有些过度，但在这种情况下是必要的！换句话说 - 有没有办法在不输入每个列名的情况下完成这个任务？总共大约有85个列。 感谢您的快速反馈。 - Anthony

@Anthony 请参考上文，对于每个表别名使用 tablename.*。 - Michael Berkowski