MySQL高级子查询重复

Question

我正在编写一些相当复杂的查询来报告我正在开发的应用程序。我可能可以通过使用更高级别的 PHP 来实现以下所有目标，但显然我想用 MySQL 来完成这一切，这显然会大大简化事情。

我需要的报表是典型的销售报表类型查询，它将列出人员列表，以及与他们相关的一些相关总计。唯一的细微差别是这个系统涉及 cargo /运输，所以“销售人员”实际上是货车司机，而“销售人员”是个人托运。此外， cargo 仅通过创建“路线”链接/绑定(bind)到各自的司机，路线记录谁在特定日期交付/收集什么。

当然，我可以使用 INNER JOIN 来获取每个司机的列表，以及他们已交付/收集的所有 cargo ，然后对从这些 cargo 中获得的收入求和。然而，当我需要显示总送货收入 和收款收入 时，问题就来了。这些数字可以来自 consignments 表，该表列出了每批货​​物。每批 cargo 都可以有一个标志(ENUM“D”，“C”)，无论是交付还是收集，它都会捐赠。这几乎可以通过使用子查询轻松确定，但仍然会有很多重复。

我目前拥有的:

SELECT pr.driver_callsign, d.first_name, d.last_name, sum(pc.revenue) AS total_revenue
FROM pallet_routes AS pr
INNER JOIN drivers AS d ON d.driver_callsign = pr.driver_callsign
INNER JOIN pallet_consignments AS pc ON pc.route_id = pr.route_id
GROUP BY pr.driver_callsign
ORDER BY d.driver_callsign ASC

这显然会返回每个司机的列表，以及他们从与他们相关的所有托运中获得的总收入。

进一步拆分此 revenue SUM 字段以显示 SUM(revenue) WHERE type = "C" 和 SUM 的最有效方法是什么(收入)WHERE type="D"？子查询？联盟？

可能还值得一提的是，最终查询将缩小到一个日期范围。因此，例如，WHERE date BETWEEN x AND y 将放在 pallet_routes 表中。

我们将不胜感激任何建议。请务必询问是否需要我详细说明。

Answer 1

我不知道您的 type 列在哪里，但如果它在 pallet_consignments 上，您可以尝试以下操作:

SELECT pr.driver_callsign, d.first_name, d.last_name, 
SUM(IF(pc.`type` = 'C', pc.revenue, 0)) collection_revenue,
SUM(IF(pc.`type` = 'D', pc.revenue, 0)) delivery_revenue
FROM pallet_routes AS pr
INNER JOIN drivers AS d ON d.driver_callsign = pr.driver_callsign
INNER JOIN pallet_consignments AS pc ON pc.route_id = pr.route_id
GROUP BY pr.driver_callsign
ORDER BY d.driver_callsign ASC

否则，请说明 type 列的位置。