mysql - 使用子查询汇总Mysql表

Question

我有一个简单的mysql表，我想总结一下。这是我的 table

表名称:hauling_trip

列:

ID, CONTRACTOR, HAULING_SHEET_NO, FROM_LOCATION, TO_LOCATION, LOAD_WEIGHT_KG, PASS_NO

数据:

1   PM  A00001  Stumping A  Pangkalan   940.00  112233
2   PM  A00002  Stumping B  Pangkalan   530.00  112233
3   PM  A00003  Stumping B  Pangkalan   970.00  112244
4   PM  A00004  Stumping A  Pangkalan   450.00  112244
5   TKF A00005  Stumping A  Pangkalan   850.00  112255
6   TKF A00006  Stumping B  Pangkalan   780.00  112255
7   TKF A00007  Stumping A  Pangkalan   903.00  112266

我想要做的是，我想通过显示每个 PASS_NO 的总行程次数和负载重量(KG)来总结此表。这是想要的结果

PASS_NO CONTRACTOR  WEIGHT_A    TRIP_A  WEIGHT_B    TRIP_B
112233  PM          940.00      1       530.00      0
112244  PM          450.00      0       970.00      1
112255  TKF         850.00      1       780.00      0
112266  TKF         903.00      1       0.00        0

相同的PASS_NO表示，拖运单被假定为1趟。例如，拖运单 A00001 和 A00002 具有相同的 PASS_NO，即 112233。因此，两个拖运单都属于 1 个行程，并且该行程属于 FROM_LOCATION 绊倒 A，因为重量大于绊倒 B。

这是我目前正在处理的查询。但我不知道如何才能顺利完成行程。

SELECT
    a.PASS_NO,
    a.CONTRACTOR,
    ( SELECT sum( LOAD_WEIGHT_KG ) FROM hauling_trip WHERE PASS_NO = a.PASS_NO AND FROM_LOCATION = 'Stumping A' ) AS WEIGTH_A,
    ( SELECT count( PASS_NO ) FROM hauling_trip WHERE PASS_NO = a.PASS_NO AND FROM_LOCATION = 'Stumping A' ) AS TRIP_A,
    ( SELECT sum( LOAD_WEIGHT_KG ) FROM hauling_trip WHERE PASS_NO = a.PASS_NO AND FROM_LOCATION = 'Stumping B' ) AS WEIGHT_B,
    ( SELECT count( PASS_NO ) FROM hauling_trip WHERE PASS_NO = a.PASS_NO AND FROM_LOCATION = 'Stumping B' ) AS TRIP_B, 
    SUM(a.LOAD_WEIGHT_KG) AS TOTAL_WEIGHT
FROM
    hauling_trip AS a 
GROUP BY
    a.PASS_NO, a.CONTRACTOR

Answer 1

使用条件聚合:

SELECT ht.PASS_NO, ht.CONTRACTOR,
       sum(case when ht.from_location = 'Stumping A' then ht.load_weight_kg end) as weight_a,
       sum(ht.from_location = 'Stumping A') as trip_a,
       sum(case when ht.from_location = 'Stumping B' then ht.load_weight_kg end) as weight_b,
       sum(ht.from_location = 'Stumping B') as trip_b
       sum(ht.LOAD_WEIGHT_KG) AS TOTAL_WEIGHT
FROM hauling_trip ht
GROUP BY ht.PASS_NO, ht.CONTRACTOR