Oracle中GREATEST函数结合OVER PARTITION BY的使用及多列查询求助

首先修正原SQL的语法错误（GROUP BY关键字顺序错误），同时针对你需要查询的非聚合列（customer_name、location等），提供两种可行方案：

方案1：扩展GROUP BY子句（适用于分组键与非聚合列一一对应场景） #

如果customerid是CUSTOMER表的主键，customer_name、location等列与customerid、aread_code一一对应，可以直接将这些列加入GROUP BY，同时计算目标聚合值：

SELECT
    c.customerid,
    c.aread_code,
    c.customer_name,
    c.location,
    c.gender,
    c.address,
    GREATEST(MAX(o.productid), MAX(o.itemid)) AS max_id_value
FROM
    CUSTOMER c
INNER JOIN
    "ORDER" o ON c.custid = o.custid
WHERE
    c.custtype = 'EXECUTIVE'
GROUP BY
    c.customerid, c.aread_code, c.customer_name, c.location, c.gender, c.address;

注意：ORDER是SQL关键字，需用双引号（部分数据库用方括号如[ORDER]）包裹避免语法错误。

方案2：使用窗口函数PARTITION BY（无需GROUP BY） #

通过窗口函数在不分组的前提下，计算每个customerid+aread_code分组的聚合值，再通过去重得到唯一的客户记录：

方式A：DISTINCT去重 #

SELECT DISTINCT
    c.customerid,
    c.aread_code,
    c.customer_name,
    c.location,
    c.gender,
    c.address,
    GREATEST(
        MAX(o.productid) OVER (PARTITION BY c.customerid, c.aread_code),
        MAX(o.itemid) OVER (PARTITION BY c.customerid, c.aread_code)
    ) AS max_id_value
FROM
    CUSTOMER c
INNER JOIN
    "ORDER" o ON c.custid = o.custid
WHERE
    c.custtype = 'EXECUTIVE';

方式B：行号去重（更灵活） #

SELECT
    customerid,
    aread_code,
    customer_name,
    location,
    gender,
    address,
    max_id_value
FROM (
    SELECT
        c.customerid,
        c.aread_code,
        c.customer_name,
        c.location,
        c.gender,
        c.address,
        GREATEST(
            MAX(o.productid) OVER (PARTITION BY c.customerid, c.aread_code),
            MAX(o.itemid) OVER (PARTITION BY c.customerid, c.aread_code)
        ) AS max_id_value,
        ROW_NUMBER() OVER (PARTITION BY c.customerid, c.aread_code ORDER BY (SELECT NULL)) AS rn
    FROM
        CUSTOMER c
    INNER JOIN
        "ORDER" o ON c.custid = o.custid
    WHERE
        c.custtype = 'EXECUTIVE'
) t
WHERE rn = 1;

关键说明 #

• 原SQL的错误点：GROUP customerid, by aread_code语法错误，正确写法为GROUP BY customerid, aread_code
• 窗口函数OVER (PARTITION BY ...)会为每个分组计算聚合值，无需将非聚合列加入GROUP BY，适合需要保留详细列同时计算分组统计的场景

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