MySQL计算燃油总费用：单车辆及全表费用汇总查询需求

Alright, let's get this sorted for you. Your current query has a couple of small syntax bugs and only returns individual fuel costs—we'll fix those and add the summary totals you need for both a specific vehicle and the entire table.

First, let's address the syntax issues in your original query:

• The DATE_FORMAT string had a typo (%y-%m-%y instead of %y-%m-%d) and a misplaced closing parenthesis
• You missed a comma between the distance and f1.fID fields
• The WHERE clause references f1.ID, but your table uses vID for vehicle IDs (I assume you meant to filter by a specific vehicle here)

Now, here are two tailored solutions depending on what you need:

Solution 1: Specific Vehicle Details + Vehicle Total Cost #

This returns all individual fuel entries for your target vehicle, plus a final row with the total cost for that vehicle:

WITH fuel_details AS (
    SELECT
        DATE_FORMAT(f1.date, '%y-%m-%d %H:%i:%s') AS date,
        f1.meter AS mileage,
        CASE WHEN f2.meter IS NULL THEN 0 ELSE f1.meter - f2.meter END AS distance,
        f1.fID,
        f1.volume,
        f1.price,
        f1.volume * f1.price AS cost
    FROM fuel f1
    LEFT OUTER JOIN fuel f2 
        ON f2.date = (
            SELECT MAX(date) 
            FROM fuel 
            WHERE fuel.vID = f1.vID AND fuel.date < f1.date
        )
    WHERE f1.vID = ? -- Replace with your target vehicle ID
)
SELECT
    date,
    mileage,
    distance,
    fID,
    volume,
    price,
    cost
FROM fuel_details
UNION ALL
SELECT
    'Total' AS date,
    NULL AS mileage,
    NULL AS distance,
    NULL AS fID,
    NULL AS volume,
    NULL AS price,
    SUM(cost) AS cost
FROM fuel_details
ORDER BY 
    CASE WHEN date = 'Total' THEN 1 ELSE 0 END,
    date;

Solution 2: Vehicle Details + Vehicle Total + Global Total #

If you want to pull three sets of data at once—the specific vehicle's entries, that vehicle's total cost, and the total cost for all vehicles in the table—use this:

-- 1. Individual entries for the target vehicle
SELECT
    'Vehicle Entries' AS result_type,
    DATE_FORMAT(f1.date, '%y-%m-%d %H:%i:%s') AS date,
    f1.meter AS mileage,
    CASE WHEN f2.meter IS NULL THEN 0 ELSE f1.meter - f2.meter END AS distance,
    f1.fID,
    f1.volume,
    f1.price,
    f1.volume * f1.price AS cost
FROM fuel f1
LEFT OUTER JOIN fuel f2 
    ON f2.date = (
        SELECT MAX(date) 
        FROM fuel 
        WHERE fuel.vID = f1.vID AND fuel.date < f1.date
    )
WHERE f1.vID = ? -- Target vehicle ID

UNION ALL

-- 2. Total cost for the target vehicle
SELECT
    'Vehicle Total Cost' AS result_type,
    NULL AS date,
    NULL AS mileage,
    NULL AS distance,
    NULL AS fID,
    NULL AS volume,
    NULL AS price,
    SUM(volume * price) AS cost
FROM fuel
WHERE vID = ? -- Same target vehicle ID

UNION ALL

-- 3. Total cost for all vehicles in the table
SELECT
    'Global Total Cost' AS result_type,
    NULL AS date,
    NULL AS mileage,
    NULL AS distance,
    NULL AS fID,
    NULL AS volume,
    NULL AS price,
    SUM(volume * price) AS cost
FROM fuel

ORDER BY 
    CASE result_type 
        WHEN 'Vehicle Entries' THEN 1 
        WHEN 'Vehicle Total Cost' THEN 2 
        WHEN 'Global Total Cost' THEN 3 
    END;

Both fixes resolve the original syntax issues and add the summary calculations you requested. Pick the solution that best fits how you plan to use the data!

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