sql - 我如何用零之和过滤掉数据集中的重复条目

Question

下面突出显示的是具有不同 trip_total_amount 的重复数据。我希望它们从我的查询结果中消失，因为它们的总和为零。

这是我获取此数据的查询:

SELECT * FROM efleet_copy AS e1
INNER JOIN
(SELECT mta_id, COUNT(mta_id) FROM efleet_copy WHERE EXTRACT(MONTH from service_date) = 02
  AND mta_id LIKE '_________'
 GROUP BY mta_id HAVING COUNT(mta_id) > 1) AS e2
ON e1.mta_id = e2.mta_id
WHERE EXTRACT(MONTH from service_date) = 02
ORDER BY e1.mta_id ASC, ride_id ASC, trip_number ASC;

Answer 1

这样试试:

with cte0 as 
(
    SELECT * FROM efleet_copy AS e1
    INNER JOIN
    (SELECT mta_id, COUNT(mta_id) FROM efleet_copy WHERE EXTRACT(MONTH from service_date) = 02
      AND mta_id LIKE '_________'
     GROUP BY mta_id HAVING COUNT(mta_id) > 1) AS e2
    ON e1.mta_id = e2.mta_id
    WHERE EXTRACT(MONTH from service_date) = 02
    ORDER BY e1.mta_id ASC, ride_id ASC, trip_number ASC
)
SELECT * from cte0 c
WHERE not exists (
    SELECT 1 FROM CTE0 ci
    WHERE c.ride_id = ci.ride_id 
    GROUP BY ride_id 
    having sum(trip_total_amount) = 0 
)