Mysql 连接 5 个表或存储过程/函数

Question

我需要加入 4 个表:位置、动物、人、组织和地址。

输入组 ID，即 70

要求:

• 根据收到的日期获取动物的最新位置。
• 从动物表中获取动物名称。

• 如果该动物有 person_id，则在位置表中，从 Person 表中获取该人的名字和姓氏以及地址信息。

• 如果位置中有组织 ID，则获取组织名称和地址来自组织表和地址表。

对于给定的组织 ID 70，如何将这 5 个表连接到一个查询中:

带有数据的示例表结构:

表格位置

 id | group_id | person_id | organization_id | fees1 |fees2 |fees3| received_date |animal_id |
  23| 70       | 12        | 0               | 10    |10    |0    |  2017-11-11   | 1        |
  24| 70       | 1         | 0               | 10    |10    |0    |  2017-10-11   | 1        |

餐 table 动物

  id| animal_name |group_id|
  1 | demo | 22   | 70

餐 table 上的人

 id | first_name | last_name |
 1 | Sam        | Dam       |

表格组织

 id | org_name |
 77 | test_org |

表地址

 id | organization_id | person_id | address1    | country|
 45 |  0              | 1         | test address| USA    |

group_id 70 的预期输出

输出:

 location.id  | location.group_id | location.person_id | location.organization_id | fees1 |fees2 |fees3| received_date | animal_id | animal_name | first_name |address1       |
 23           | 70                |  1                 | 70                       | 20    |20    |20    | 2017-11-11   |   1       |  demo        |  Sam       | test address |

Answer 1

您可以尝试以下查询

select  l.id,l.group_id,l.person_id,l.organization_id,l.fees1,l.fees2,l.fees3,max(l.received_date) as received_date,l.animal_id,p.first_name,aa.address1 from  Organization as o inner join location as l on o.id=l.organization_id left join Person as p on l.person_id=p.id inner join Animal as a on l.animal_id=a.id  inner join Address as aa on o.id=aa.organization_id where o.id=70 limit 1

或

select  l.id,l.group_id,l.person_id,l.organization_id,l.fees1,l.fees2,l.fees3,l.received_date,l.animal_id,p.first_name,aa.address1 from  Organization as o inner join location as l on o.id=l.organization_id left join Person as p on l.person_id=p.id inner join Animal as a on l.animal_id=a.id  inner join Address as aa on o.id=aa.organization_id where o.id=70  order by l.received_date desc limit 1