我设计了以下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。

谢谢! :)

最佳答案

首先，在您当前的表架构中，您无法区分在不同机票中出售给同一乘客的服务。因此，您无法正确计算每张票的 total_cost。您的 service 表中必须有 ticket_id。

现在，如果您在 service 表中有一个 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

关于mysql - 多个子查询的SQL SUM操作，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/18201600/