sql - 如何为三个表编写此查询

Question

我有 3 张桌子

表：代理

Id | Name
---+------
 1 | a
 2 | b
 3 | c

表：乘客

Id | AgentReference | Name
---+----------------+-----
 1 |       1        | E
 2 |       1        | F
 3 |       3        | ddd

表：visa_by_agent

Id | agentId | visa_id | Total | Used
---+---------+---------+-------+------
 1 |   1     | aa      |   10  |   1
 2 |   1     | bb      |   20  |   2
 3 |   3     | dd      |   10  |   5

我想显示结果

agentId | agentName | TotalPassenger | TotalVisa | TotalUsed
--------+-----------+----------------+-----------+-----------
   1    | a         |      2         |     30    |     3
   2    | b         |      0         |      0    |     0
   3    | c         |      1         |     10    |     5

我试过这个并得到语法错误

select  
    a.Id, a.Name, 
    vba.total, vba.used p.num_passengers 
from 
    agents a 
left join 
    (select 
         vba.AgentId, sum(Total) as Total, sum(Used) as Used 
     from 
         visa_by_agent vba 
     group by 
         vba.AgentId) vba on a.Id = vba.AgentId 
left join 
    (select p.AgentReference, count(*) as num_passengers 
     from passengers p 
     group by p.AgentReference) p on a.Id = p.AgentReference 
group by 
    agents.Id

标签： sql

Answer 1

尝试子查询而不是连接。

SELECT 
  id AS agentId,
  name AS agentName,
  (SELECT count(*) FROM passengers WHERE agentId = a.id) AS TotalPassenger,
  (SELECT COALESCE(sum(Total), 0) FROM visa_by_agent WHERE agentId=a.id) AS TotalVisa
FROM
  agents AS a;