Mysql - 如何在左连接中为整个表设置别名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7150209/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql - How to alias a whole table in a left join
提问by Anthony
I have a situation where a property table holds an address id (from the g_addresses table) and an applicant table alsoholds an address id from the g_addresses. I'd like to left join these together but select all the fields in the table.
我有一种情况，属性表包含地址 ID（来自 g_addresses 表），而申请人表也包含来自 g_addresses 的地址 ID。我想将这些组合在一起，但选择表中的所有字段。
I know of using 'as' to make an alias for fields, but is there any way to produce an alias for a whole table?
我知道使用“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
This produces a result containing only one address row and not both, The row that is returned is the row from the final join and not the one previously, indicating it is overwriting when it is returned.
这会产生一个只包含一个地址行而不是两个地址行的结果，返回的行是来自最终连接的行而不是之前的行，表明它在返回时被覆盖。
回答by Andriy M
I don't think you should use masked references, like *or `reference`.*, in your case, because you may end up with a row set containing identical column names (id, address_id).
我认为您不应该使用掩码引用，例如*或`reference`.*，在您的情况下，因为您最终可能会得到一个包含相同列名 ( id, address_id)的行集。
If you want to pull all the columns from the joined tables, you should probably specify them individually in the SELECT clause and assign a unique alias to every one of them:
如果您想从连接表中提取所有列，您可能应该在 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
回答by Michael Berkowski
Be more specific about columns you select
更具体地了解您选择的列
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
