SQL SUM多个子查询的操作 [英] SQL SUM operation of multiple subqueries
问题描述
我设计了以下mysql数据库表，
i have the following mysql database table designed,
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.
我尝试了以下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
尽管，该结果将获得的乘客票价总和作为total_fare，但是对于服务成本，它仅求和并返回第一位乘客的总服务成本.
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.
我认为我需要更多的查询嵌套，需要帮助，如果可能的话，我如何获得总计乘客票价和服务费用总和的结果.
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.
好的，这是要澄清的sql插入语句，
OK here is the sql insert statment to clarify,
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);
我想获取机票'TK01'的总费用(包括总票价和服务费). sql必须返回总票价为120 + 120 = 240，总服务返回为30 + 50 + 50 = 130.
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.
谢谢！ :)
推荐答案
首先，在当前表架构中，您无法区分以不同票证出售给同一位乘客的服务.因此，您无法正确计算每张票证的total_cost.您必须在service表中包含ticket_id.
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_cost per ticket. You have to have ticket_id in your service table.
现在，如果要在service表中具有ticket_id，则具有相关子查询的解决方案可能看起来像
Now, if you were to have a ticket_id in service table then a solution with a correlated subqueries might look like
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
或带有JOIN s
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.
现在使用您的当前架构
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
和
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
只需获得每张票的总计
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
或
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
这篇关于SQL SUM多个子查询的操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
