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

Question

我需要加入四个桌子:地点，动物，人，组织和地址。

输入将是组id ie 70

所需经费：

• 获取动物的最新位置，根据收到的日期。
• 从动物餐桌上得到动物的名字。
• 如果该动物有person_id，则在location表中，从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        |

餐桌动物

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

餐桌人

 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