MySQL 多个子查询的SQL SUM操作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18201600/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL SUM operation of multiple subqueries
提问by Dipendra Gurung
i have the following mysql database table designed,
我设计了以下mysql数据库表，
ticket(id, code, cust_name);
passenger(id, ticket_id, name, age, gender, fare);
service(id, passenger_id, item, cost);
A ticket can have many passenger and each passenger can have multiple services purchased. What I want is to get the grand total of each ticket cost.
一张票可以有很多乘客，每个乘客可以购买多项服务。我想要的是获得每张门票费用的总计。
I have tried the following sql,
我试过下面的sql，
SELECT
SUM(fare) as total_fare,
(SELECT SUM(cost) as total_cost FROM services WHERE passenger.id = services.passenger_id) as total_service_cost
FROM
ticket
JOIN passenger ON passenger.ticket_id = ticket.id
Though, the result gets the total of passenger fare as total_fare but for the service cost, it sums and returns the first passenger's total service cost only.
虽然，结果将乘客票价的总和作为 total_fare 但对于服务成本，它仅求和并返回第一位乘客的总服务成本。
I thinks i need some more nesting of queries, need help and if possible please how can i get the result as grand total summing up both passenger fare and service cost total.
我想我需要更多的查询嵌套，需要帮助，如果可能的话，我怎样才能得到总乘客票价和服务成本总和的结果。
OK here is the sql insert statment to clarify,
好的，这里是要澄清的 sql insert 语句，
INSERT INTO `ticket` (`id`, `code`, `cust_name`) VALUES
(1, 'TK01', 'Dipendra Gurung');
INSERT INTO `passenger` (`id`, `ticket_id`, `name`, `age`, `gender`, `fare`) VALUES
(1, 1, 'John', '28', 'M', 120),
(2, 1, 'Kelly', '25', 'F', 120);
INSERT INTO `services` (`id`, `passenger_id`, `item`, `cost`) VALUES
(1, 1, 'S1', 30),
(2, 1, 'S2', 50),
(3, 2, 'S3', 50);
I want to get the total cost of the ticket 'TK01' (including total fare and services total). The sql must return total fare as 120+120 = 240 and total services as 30+50+50 = 130.
我想获得机票“TK01”的总费用（包括总票价和服务总额）。sql 必须返回总票价为 120+120 = 240，总服务数为 30+50+50 = 130。
Thanks! :)
谢谢！:)
回答by peterm
First of all in your current table schema you have no way to distinguish between services that have been sold to the same passenger in different tickets. Therefore you have no way to correctly calculate total_costper ticket. You have to have ticket_idin your servicetable.
首先，在您当前的表模式中，您无法区分以不同机票出售给同一位乘客的服务。因此，您无法正确计算total_cost每张票。你必须ticket_id在你的service桌子上。
Now, if you were to have a ticket_idin servicetable then a solution with a correlated subqueries might look like
现在，如果您有一个ticket_idinservice表，那么具有相关子查询的解决方案可能看起来像
SELECT t.*,
(SELECT SUM(fare)
FROM passenger
WHERE ticket_id = t.id) total_fare,
(SELECT SUM(cost)
FROM service
WHERE ticket_id = t.id) total_cost
FROM ticket t
or with JOINs
或与JOINs
SELECT t.id,
p.fare total_fare,
s.cost total_cost
FROM ticket t LEFT JOIN
(
SELECT ticket_id, SUM(fare) fare
FROM passenger
GROUP BY ticket_id
) p
ON t.id = p.ticket_id LEFT JOIN
(
SELECT ticket_id, SUM(cost) cost
FROM service
GROUP BY ticket_id
) s
ON t.id = s.ticket_id
Note:Both queries take care of the fact that passenger can have multiple services per ticket or no services at all.
注意：这两个查询都考虑了这样一个事实，即乘客可以在每张票上有多项服务，也可以根本没有服务。
Now with your current schema
现在使用您当前的架构
SELECT t.*,
(SELECT SUM(fare)
FROM passenger
WHERE ticket_id = t.id) total_fare,
(SELECT SUM(cost)
FROM service s JOIN passenger p
ON s.passenger_id = p.id
WHERE p.ticket_id = t.id) total_cost
FROM ticket t
and
和
SELECT t.id,
p.fare total_fare,
s.cost total_cost
FROM ticket t LEFT JOIN
(
SELECT ticket_id, SUM(fare) fare
FROM passenger
GROUP BY ticket_id
) p
ON t.id = p.ticket_id LEFT JOIN
(
SELECT p.ticket_id, SUM(cost) cost
FROM service s JOIN passenger p
ON s.passenger_id = p.id
GROUP BY p.ticket_id
) s
ON t.id = s.ticket_id
Just to get a grand total per ticket只是为了获得每张票的总数
SELECT t.*,
(SELECT SUM(fare)
FROM passenger
WHERE ticket_id = t.id) +
(SELECT SUM(cost)
FROM service s JOIN passenger p
ON s.passenger_id = p.id
WHERE p.ticket_id = t.id) grand_total
FROM ticket t
or
或者
SELECT t.id,
p.fare + s.cost grand_total
FROM ticket t LEFT JOIN
(
SELECT ticket_id, SUM(fare) fare
FROM passenger
GROUP BY ticket_id
) p
ON t.id = p.ticket_id LEFT JOIN
(
SELECT p.ticket_id, SUM(cost) cost
FROM service s JOIN passenger p
ON s.passenger_id = p.id
GROUP BY p.ticket_id
) s
ON t.id = s.ticket_id
回答by SpaceDog
You can just join the three tables together, then you can do the SUMs directly without the subselect. You'll need to use GROUP BYto group by ticket.id if you want it per ticket.
您可以将三个表连接在一起，然后您可以在没有子选择的情况下直接进行 SUM。GROUP BY如果你想要每张票，你需要使用to group by ticket.id。
Something like:
就像是：
SELECT t.id, SUM(p.fare) AS total_far, SUM(s.cost) AS total_cost
FROM
ticket t, passenger p, service s
WHERE t.id = p.ticket_id AND s.passenger_id = p.id
GROUP BY t.id;
回答by Anurag
SELECT
SUM(fare) as total_fare,
SUM(cost) as total_service_cost
FROM
ticket
left join passenger ON ticket.id = passenger.ticket_id
left join service ON passenger.id = service.passenger_id
回答by Noel
How about this?
这个怎么样？
with fares as(
select p.id id, p.ticket_id ticket_id, sum(coalesce(s.cost,0)) cost
from passenger p left outer join service s
on p.id = s.passenger_id
group by p.id, p.ticket_id)
select q.ticket_id, sum(f.cost), sum(q.fare), sum(f.cost + q.fare)
from fares f inner join passenger q
on f.id = q.id
group by q.ticket_id;
回答by StanislavL
What about this?
那这个呢？
SELECT
SUM(fare) as total_fare,
SUM(cost) as total_cost as total_service_cost
FROM
ticket
JOIN passenger ON passenger.ticket_id = ticket.id
JOIN service ON passenger.id = service.passenger_id
If you need to sum for each ticket then add GROUP BY ticket.id
如果您需要为每张票求和，则添加 GROUP BY ticket.id
回答by Roman Pekar
select
t.code,
sum(p.fare) as total_fare,
sum(s.cost) as total_cost
from ticket as t
inner join passenger as p on p.ticket_id = t.id
inner join service as s on s.passenger_id = p.id
group by t.code
