MySQL:如何多次加入同一个表?

2021-11-20 00:00:00 sql left-join group-by mysql

我有两个表 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.

相关文章