MySQL技术问询：指定ID组在日期范围内的最新值求和

Got it, let's fix this query to get exactly what you need. Instead of summing all matching values, we first need to pull the most recent record for each tag_id (within your time range and shift=1 condition), then sum those latest values.

Method 1: Using Window Functions (MySQL 8.0+) #

This is the cleanest approach if you're on a newer MySQL version that supports window functions:

SELECT SUM(CAST(hd.value AS SIGNED)) AS case_count
FROM (
    SELECT 
        value,
        -- Assign a row number per tag_id, ordered by newest timestamp first
        ROW_NUMBER() OVER (PARTITION BY tag_id ORDER BY timestamp DESC) AS rn
    FROM historical_data hd
    WHERE 
        hd.tag_id IN (45,109,173,237,301,365,429) 
        AND hd.shift = 1 
        AND hd.timestamp BETWEEN '2018-04-10' AND '2018-04-11'
) AS latest_records
-- Only keep the newest record per tag_id (rn=1)
WHERE rn = 1;

How this works: #

• The inner query groups records by tag_id (via PARTITION BY tag_id) and sorts each group by timestamp in descending order. The ROW_NUMBER() function labels the newest record in each group as rn=1.
• The outer query filters for only those rn=1 records, then converts their value to a signed integer and sums them up.

Method 2: For MySQL 5.x (No Window Function Support) #

If you're stuck on an older MySQL version, use a correlated subquery to find the latest timestamp per tag_id:

SELECT SUM(CAST(hd.value AS SIGNED)) AS case_count
FROM historical_data hd
WHERE 
    hd.tag_id IN (45,109,173,237,301,365,429) 
    AND hd.shift = 1 
    AND hd.timestamp BETWEEN '2018-04-10' AND '2018-04-11'
    -- Match only the record with the latest timestamp for this tag_id/shift/time range
    AND hd.timestamp = (
        SELECT MAX(timestamp)
        FROM historical_data
        WHERE tag_id = hd.tag_id AND shift = 1 AND timestamp BETWEEN '2018-04-10' AND '2018-04-11'
    );

Notes: #

• Make sure your timestamp column is a DATETIME or TIMESTAMP type to ensure accurate sorting and comparison.
• If a tag_id has no matching records in the specified conditions, it won't contribute to the sum (which is usually the desired behavior).

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