如何在SQL中根据客户当日出行次数计算票价？

你的查询出现Single票费用异常累加的问题，核心原因是错误地将TicketCosts表直接关联到主查询中——这个表里存在Single和Day两条票种记录，导致每条出行记录都会和这两条票种各关联一次，行数直接翻倍，最终SUM计算时重复累加了费用。

举个实际场景：如果客户当日出行2次，主查询中原本有2条出行记录，关联TicketCosts后会生成4条记录（2条对应Single票、2条对应Day票）。此时CASE语句中Single票的分支是tc.cost*2，SUM时会把这2条Single票记录的结果相加，得到2*(tc.cost*2)=4*tc.cost，比预期的2倍费用多了一倍，这就是你看到的累加异常。

修正后的查询语句 #

正确思路是先统计每个客户每日的出行次数，再根据次数匹配对应票价，避免关联TicketCosts导致的行数膨胀：

SELECT 
    ct.c_id,
    DATE(bt.start_time) AS date,
    CASE
        -- 出行1次，取Single票价
        WHEN trip_count = 1 THEN (SELECT cost FROM TicketCosts WHERE duration = 'Single')
        -- 出行2次，取Single票价×2
        WHEN trip_count = 2 THEN (SELECT cost FROM TicketCosts WHERE duration = 'Single') * 2
        -- 出行3次及以上，取Day票价
        WHEN trip_count >= 3 THEN (SELECT cost FROM TicketCosts WHERE duration = 'Day')
        ELSE 0
    END AS total_cost
FROM CustomerTrip ct
JOIN BusTrip bt ON ct.b_id = bt.b_id
JOIN (
    SELECT 
        b_id,
        DATE(start_time) AS trip_date,
        COUNT(*) AS trip_count
    FROM BusTrip 
    GROUP BY b_id, DATE(start_time)
) trip_counts ON ct.b_id = trip_counts.b_id AND DATE(bt.start_time) = trip_counts.trip_date
GROUP BY ct.c_id, DATE(bt.start_time), trip_count;

更高效的写法（提前获取票价） #

如果TicketCosts里只有Single和Day两种固定票价，可以用CTE提前获取票价，避免多次子查询：

WITH TicketPrices AS (
    SELECT 
        MAX(CASE WHEN duration = 'Single' THEN cost END) AS single_cost,
        MAX(CASE WHEN duration = 'Day' THEN cost END) AS day_cost
    FROM TicketCosts
    WHERE duration IN ('Single', 'Day')
)
SELECT 
    ct.c_id,
    DATE(bt.start_time) AS date,
    CASE
        WHEN trip_count = 1 THEN tp.single_cost
        WHEN trip_count = 2 THEN tp.single_cost * 2
        WHEN trip_count >= 3 THEN tp.day_cost
        ELSE 0
    END AS total_cost
FROM CustomerTrip ct
JOIN BusTrip bt ON ct.b_id = bt.b_id
JOIN (
    SELECT 
        b_id,
        DATE(start_time) AS trip_date,
        COUNT(*) AS trip_count
    FROM BusTrip 
    GROUP BY b_id, DATE(start_time)
) trip_counts ON ct.b_id = trip_counts.b_id AND DATE(bt.start_time) = trip_counts.trip_date
CROSS JOIN TicketPrices tp
GROUP BY ct.c_id, DATE(bt.start_time), trip_count, tp.single_cost, tp.day_cost;

关键说明 #

• 移除了和TicketCosts的直接JOIN，改用子查询或CTE获取票价，彻底避免行数膨胀导致的重复计算。
• 分组时包含trip_count及票价字段，确保CASE分支能精准匹配每个客户每日的出行次数。

内容的提问来源于stack exchange，提问作者Stroql