我有两个表 ticket
和 attr
.表 ticket
有 ticked_id
字段和其他几个字段.表 attr
有 3 个字段:
I have two tables ticket
and attr
. Table ticket
has ticked_id
field and several other fields. Table attr
has 3 fields:
ticket_id - numeric
attr_type - numeric
attr_val - string
attr_type
是一个固定的枚举值.例如,它可以是 1
、2
或 3
.
attr_type
is a fixed enum of values. For example, it can be 1
, 2
or 3
.
我需要进行查询,查询结果将是 4 列:
I need to make a query, the result of which will be 4 columns:
ticket_id
,attr_val
用于attr_type=1
,attr_val
用于attr_type=2
>, attr_val
for attr_type=3
ticket_id
, attr_val
for attr_type=1
, attr_val
for attr_type=2
, attr_val
for attr_type=3
如果attr
表中没有attr_type
对应的值,则对应列中应显示NULL值.
If there is no corresponding value for attr_type
in attr
table then NULL value should be shown in corresponding column.
示例:
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
结果应该是:
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
我尝试了 3 次左加入 attr
表,但无法弄清楚如何通过 attr_type
I tried left joining attr
table 3 times, but cannot figure out how to arrange output by attr_type
你需要使用多个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
这是一个例子:SQL Fiddle.
这篇关于MySQL:如何多次加入同一个表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!