Oracle中添加左连接并新增最高温度列的SQL报错求助

Let's walk through fixing your SQL issue step by step—you've got a couple of syntax snags and a quick logical tweak to get exactly what you need:

First, the root causes of your ORA-00933 error: #

• Oracle doesn't allow column aliases in GROUP BY: You tried grouping by typ.name as Silo_name, but Oracle requires you to use the original column name (typ.name) instead of the alias you defined in the SELECT clause.
• Incorrect column alias: You accidentally named the temperature column "Lowest salary" instead of "最高温度" (the column you actually need).
• Unnecessary JOIN complexity: Joining directly to TEMPR_SILO can create duplicate rows, forcing you to use GROUP BY when a cleaner approach exists.

Solution 1: Correlated Subquery (Matches Your Original Logic) #

This uses the exact subquery logic you outlined, embedded directly in the SELECT list. It avoids GROUP BY entirely and maintains your original LEFT JOIN behavior:

SELECT 
    typ.name as Silo_name,
    tr.DEVID,
    tr.name as dev_name,
    (
        -- Get max formatted temp for the latest ID_TRANS batch, filtered to the device's sensors
        SELECT MAX(to_char(TEMP,'99.99'))
        FROM TEMPR_SILO ts
        WHERE ts.ID_TRANS = (SELECT MAX(ID_TRANS) FROM TEMPR_SILO)
          AND ts.NAME IN (SELECT NAME FROM SILO_SENSOR WHERE DEVICES_ID = tr.DEVID)
    ) AS "最高温度"
FROM HANGINGTHREAD_SILO ev
LEFT JOIN SILO typ ON ev.ID_SILO = typ.id
LEFT JOIN IOT_DEVICES tr ON ev.DEVICES_ID = tr.id;

Why this works:

• The correlated subquery runs once per row in your main query, fetching the correct max temperature for each device.
• If there's no temperature data for a device, the "最高温度" column will return NULL (keeping your original LEFT JOIN behavior).
• No GROUP BY means no alias-related syntax errors.

Solution 2: CTE-Based Approach (Better for Large Datasets) #

If you're working with big tables, this precomputes temperature data upfront for better performance:

WITH latest_temperature_batch AS (
    -- Get max temp per sensor from the most recent ID_TRANS batch
    SELECT 
        ts.NAME,
        MAX(to_char(ts.TEMP,'99.99')) AS max_formatted_temp
    FROM TEMPR_SILO ts
    WHERE ts.ID_TRANS = (SELECT MAX(ID_TRANS) FROM TEMPR_SILO)
    GROUP BY ts.NAME
),
device_temp_map AS (
    -- Link sensors to their devices, ensuring one row per device
    SELECT 
        ss.DEVICES_ID,
        ltb.max_formatted_temp
    FROM SILO_SENSOR ss
    JOIN latest_temperature_batch ltb ON ss.NAME = ltb.NAME
    GROUP BY ss.DEVICES_ID, ltb.max_formatted_temp
)
SELECT 
    typ.name as Silo_name,
    tr.DEVID,
    tr.name as dev_name,
    dtm.max_formatted_temp AS "最高温度"
FROM HANGINGTHREAD_SILO ev
LEFT JOIN SILO typ ON ev.ID_SILO = typ.id
LEFT JOIN IOT_DEVICES tr ON ev.DEVICES_ID = tr.id
LEFT JOIN device_temp_map dtm ON tr.DEVID = dtm.DEVICES_ID;

Why this works:

• The CTEs precompute the latest temperature data and map it to devices, reducing repeated subquery execution.
• It still maintains LEFT JOIN behavior, so you won't lose rows from HANGINGTHREAD_SILO if no temperature data exists.

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