MySQL：我如何多次加入同一个表？ [英] MySQL: How do I join same table multiple times?

问题描述

我有两张表票据和 attr 。表票据有 ticked_id 字段和其他几个字段。表 attr 有3个字段：

  ticket_id  - 数字
 attr_type  - 数字
 attr_val  - 字符串
  

attr_type 是一个固定值的枚举。例如，它可以是 1 ， 2 或 3 。

我需要做一个查询，其结果将是4列：

ticket_id ， attr_val for attr_type = 1 ， attr_val for attr_type = 2 ， attr_val for attr_type = 3

如果 attr_type 在中没有相应的值，

示例：

  ticket 
 ticket_id：1 
 $ b $ ticket_id：2 
 
 ticket_id：3 
 
 
 attr 
 ticket_id：1 
 attr_type：1 
 attr_val：Foo 
 $ b $ ticket_id：1 
 attr_type：2 
 attr_val：Bar 
 
 ticket_id：1 
 attr_type：3 
 attr_val：蛋
 $ b ticket_id：2 
 attr_type：2 
 attr_val：垃圾邮件
  

th e结果应该是：

$ pre $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ attr_val3：Egg

ticked_id：2
attr_val1：NULL
attr_val2：垃圾邮件
attr_val3：NULL

ticked_id：3
attr_val1：NULL
attr_val2：NULL
attr_val3：NULL


attr 表3次，但无法弄清楚如何按 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小提琴。

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 is a fixed enum of values. For example, it can be 1, 2 or 3.

I need to make a query, the result of which will be 4 columns:

ticket_id, attr_val for attr_type=1, attr_val for attr_type=2, attr_val for attr_type=3

If there is no corresponding value for attr_type in attr table then NULL value should be shown in corresponding column.

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 attr table 3 times, but cannot figure out how to arrange output by attr_type

解决方案

You need to use multiple 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.

这篇关于MySQL：我如何多次加入同一个表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！