如何解决SELECT列表中列未在聚合函数或GROUP BY子句的错误

核心错误原因 #

第一个LEFT OUTER JOIN的子查询存在语法错误：GROUP BY ME_ID仅按ME_ID分组，但SELECT列表中包含truck_ID、hazmat、product_description这些非聚合列，且未将它们加入GROUP BY子句。SQL要求分组查询中，未使用聚合函数的列必须全部包含在GROUP BY里，否则数据库无法确定要返回这些列的哪一行数据。

另外你的WHERE子句存在逻辑优先级问题：AND的优先级高于OR，导致只有第一个地址条件会和年份条件联动，其余地址条件会忽略2022年的限制，必须用括号将所有地址条件包裹起来。

修正后的查询语句 #

假设你的需求是：对table2中每个truck_ID计算总重量，同时取该truck_ID下最新freight_ID对应的hazmat和product_description，修正后的SQL如下：

select 
    l.me_id,
    l.CREATED_DATE,
    st.location_name,
    st.location_address_1,
    st.location_city,
    st.location_state_code,
    st.location_postal_code,
    l.mode_type,
    li.total_weight as weight,
    li.hazmat,
    li.PRODUCT_DESCRIPTION
from tp.table l

LEFT OUTER JOIN
(
    SELECT m.truck_ID, m.hazmat, m.total_weight, m.product_description
    FROM (
        SELECT 
            truck_ID,
            hazmat,
            SUM(weight) OVER(PARTITION BY truck_ID) AS total_weight,
            product_description,
            ROW_NUMBER() OVER(PARTITION BY truck_ID ORDER BY freight_ID DESC) AS R_NO
        FROM [TP].[table2] 
        where status='active'
    ) m 
    where R_NO=1
) li ON L.truck_ID=li.truck_id

LEFT OUTER JOIN
(
    Select n.* from
    (
        SELECT ME_ID, location_name, location_address_1, location_city, location_state_code,location_postal_code, 
        Row_number() OVER(PARTITION BY ME_ID ORDER BY stop_ID DESC) AS R_NO
        FROM [TP].[table3] 
        where status='active' and STOP_SEQUENCE_NUMBER = '1'
    ) n where R_NO=1
) st ON L.truck_ID=st.truck_id

where year(l.CREATED_date) = '2022' 
AND (
    LOCATION_address_1 LIKE '%6210 GLENWAY%'
    OR LOCATION_address_1 LIKE '%480 WILEY%'
    OR LOCATION_address_1 LIKE '%575 5TH%' -- 数据中不存在
    OR LOCATION_address_1 LIKE '%100 E ROOSEVELT%'
    OR LOCATION_address_1 LIKE '%565 5TH%' -- 数据中不存在
    OR LOCATION_address_1 LIKE '%1001 SE TV%'
    OR LOCATION_address_1 LIKE '%435 HUDSON%'
    OR LOCATION_address_1 LIKE '%575 FIFTH%'-- 数据中不存在
    OR LOCATION_address_1 LIKE '%3940 PLANK%'
    OR LOCATION_address_1 LIKE '%609 SW 8TH%'
    OR LOCATION_address_1 LIKE '%28145 HARRISON%' 
    OR LOCATION_address_1 LIKE '%2620 SW 17Th%'
    OR LOCATION_address_1 LIKE '%833 W 16TH%'
    OR LOCATION_address_1 LIKE '%992 POQUONNOCK%'
    OR LOCATION_address_1 LIKE '%4555 DANVERS%'
    OR LOCATION_address_1 LIKE '%100 COMMERCE DR%'
    OR LOCATION_address_1 LIKE '%6600 JEFFERSON%'
)

其他说明 #

如果你的需求是按ME_ID分组计算总重量，同时取该ME_ID下最新记录的truck_ID、hazmat等字段，可以调整子查询逻辑：先聚合计算总重量，再关联获取最新的单条记录，避免分组和非聚合列的冲突。

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