MySQL：如何多次加入同一个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12561733/
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 do I join same table multiple times?
提问by rmflow
I have two tables ticketand attr. Table tickethas ticked_idfield and several other fields. Table attrhas 3 fields:
我有两张桌子ticket和attr. 表ticket有ticked_id字段和其他几个字段。表attr有 3 个字段：
ticket_id - numeric
attr_type - numeric
attr_val - string
attr_typeis a fixed enum of values. For example, it can be 1, 2or 3.
attr_type是一个固定的枚举值。例如，它可以是1,2或3。
I need to make a query, the result of which will be 4 columns:
我需要进行查询，其结果将是 4 列：
ticket_id, attr_valfor attr_type=1, attr_valfor attr_type=2, attr_valfor attr_type=3
ticket_id,attr_val对于attr_type=1,attr_val对于attr_type=2,attr_val对于attr_type=3
If there is no corresponding value for attr_typein attrtable then NULL value should be shown in corresponding column.
如果不存在用于相应值attr_type在attr表然后NULL值应在相应的列中显示。
Example:
例子：
ticket
ticket_id: 1
ticket_id: 2
ticket_id: 3
attr
ticket_id: 1
attr_type: 1
attr_val: Foo
ticket_id: 1
attr_type: 2
attr_val: Bar
ticket_id: 1
attr_type: 3
attr_val: Egg
ticket_id: 2
attr_type: 2
attr_val: Spam
the result should be:
结果应该是：
ticked_id: 1
attr_val1: Foo
attr_val2: Bar
attr_val3: Egg
ticked_id: 2
attr_val1: NULL
attr_val2: Spam
attr_val3: NULL
ticked_id: 3
attr_val1: NULL
attr_val2: NULL
attr_val3: NULL
I tried left joining attrtable 3 times, but cannot figure out how to arrange output by attr_type
我尝试了attr3 次左加入表，但无法弄清楚如何安排输出attr_type
回答by András Ottó
You need to use multiple LEFT JOINs:
您需要使用多个LEFT JOINs：
SELECT
ticket.ticket_id,
a1.attr_val AS attr_val1,
a2.attr_val AS attr_val2,
a3.attr_val AS attr_val3
FROM ticket
LEFT JOIN attr a1 ON ticket.ticket_id=a1.ticket_id AND a1.attr_type=1
LEFT JOIN attr a2 ON ticket.ticket_id=a2.ticket_id AND a2.attr_type=2
LEFT JOIN attr a3 ON ticket.ticket_id=a3.ticket_id AND a3.attr_type=3
Here is an example: SQL Fiddle.
这是一个示例：SQL Fiddle。
回答by rmflow
While you can use aliased left joins, in this case you could also use a combination of grouping and conditional expressions:
虽然您可以使用别名左连接，但在这种情况下，您还可以使用分组和条件表达式的组合：
select t.ticket_id,
max(case when a.attr_type=1 then a.attr_val end) attr_val1,
max(case when a.attr_type=2 then a.attr_val end) attr_val2,
max(case when a.attr_type=3 then a.attr_val end) attr_val3
from ticket t
left join attr a on t.ticket_id = a.ticket_id
group by t.ticket_id
回答by podiluska
You use table aliases
您使用表别名
eg:
例如：
Select
ticket.ticket_id,
a1.attr_val as attr_val1,
a2.attr_val as attr_val2,
a3.attr_val as attr_val3
from ticket
left join (select * from attr where attr_type=1) a1 on ticket.ticket_id=a1.ticket_id
left join (select * from attr where attr_type=2) a2 on ticket.ticket_id=a2.ticket_id
left join (select * from attr where attr_type=3) a3 on ticket.ticket_id=a3.ticket_id
