多个子查询的SQL SUM操作

Question

我有一个设计好的MySQL数据库表，如下所示：

ticket(id, code, cust_name);
passenger(id, ticket_id, name, age, gender, fare);
service(id, passenger_id, item, 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，但对于服务费用，它仅累加并返回第一名乘客的总服务费用。

我认为我需要更多查询嵌套，需要帮助，如果可能的话，请告诉我如何得到总金额，将乘客票价和服务费用合计。

好的，这是SQL插入语句以澄清问题：

---

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。谢谢！ :)

- Dipendra Gurung

---

你如何区分已售给同一乘客的服务在不同机票中的情况？看起来service表中缺少ticket_id... - peterm

这里使用了服务表来存储乘客购买的服务项目，并通过乘客ID外键约束与乘客表相关联。 - Dipendra Gurung

Answer 1

首先，在您当前的表模式中，没有办法区分已经在不同票证中向同一乘客出售的服务。因此，您无法正确计算每张票证的总费用。您需要在您的“服务”表中拥有“票证编号（ticket_id）”。

现在，如果您在“服务”表中有一个“票证编号（ticket_id）”，那么使用相关子查询的解决方案可能如下所示：

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

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

注意：这两个查询都考虑到了乘客可能有多张车票或者没有车票服务的情况。

---

现在考虑您当前的模式

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

- peterm

---

@DipendraGurung 请查看更新后的答案，以获取您当前表结构的解决方案。 - peterm

谢谢！@Peterm，我想再补充一件事，如何将这两个子查询添加到一起以获得一个名为grand_total的字段？感谢您的时间。 :) - Dipendra Gurung

@DipendraGurung 你只需要总费用还是想看到总车费、总成本和总费用？ - peterm

我只需要总数。 - Dipendra Gurung

Answer 2

你可以将这三个表格连接在一起，然后就可以直接进行SUM计算而不需要使用子查询。如果你希望每张票据的结果都有，就需要使用GROUP BY来按ticket.id进行分组。

类似这样：

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;

- SpaceDog

---

谢谢！当我运行你的查询时，total_cost被正确地总结了，但是total_fare不正确！ - Dipendra Gurung

@Jack 如果一位乘客有两个或更多的服务，则您的总费用将为费用*每位乘客的服务数量。在我看来，这就是为什么它是错误的原因。此外，应该使用OUTER JOIN到服务，因为人可能没有服务。 - peterm

@peterm，关于使用OUTER JOIN来捕获人们没有服务的情况，你说得很好。为了避免其他问题，您可以计算唯一服务的数量并将其除以总数。 - SpaceDog

@peterm 是的，我也在想这个问题——目前我无法进行测试，但既然我们已经按票号分组，我认为(SUM(s.cost) \ COUNT(s.cost)) AS total_cost应该可以工作。虽然我怀疑对于大量数据来说，子查询可能会更快一些。 - SpaceDog

@SpaceDog 问题是如果一位乘客（或者在同一张车票内的多位乘客）拥有多项价格相同的服务，那么COUNT(s.cost)将会是1而不是N。 - peterm

Answer 3

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

- Anurag

---

我认为你缺少了一个 group by :) - Ja͢ck

Answer 4

这个怎么样？

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;

- Noel

Answer 5

这个怎么样？

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

如果您需要为每张票据求和，则添加GROUP BY ticket.id。

- StanislavL

---

你的查询语句第三行是否允许使用双引号作为别名？我在运行该查询时遇到了错误。 - Dipendra Gurung

Answer 6

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

- Roman Pekar