Oracle Toad中仅按指定列进行SQL分组的实现方案咨询

我编写了如下SQL查询，由于subject_code、category_code、assortment_code存在多条重复记录，希望仅按这三列进行分组。但我需要在SELECT子句中保留其他列，若将这些列加入GROUP BY会导致结果不符合预期，请问有什么可行的解决方法？

原查询语句：

select rts.MERCH_DIV_NUM, min(rts.DEPT_NUM) DEPT_NUM, bc.subject_code, bc.category_code,
       rts.ASSORTMENT_CODE, rts.CURR_PEND_QTY, 
       rts.CURR_PEND_AMT, a.assortment_desc
from rts_subcat_dept_pending rts
inner join bn_assortment a on rts.assortment_code = a.assortment_code
inner join bn_category bc on substr(rts.assortment_code, 0, 4) = bc.category_code      
group by bc.subject_code, bc.category_code, rts.ASSORTMENT_CODE

下面是几种可行的解决思路：

方法1：用窗口函数筛选每组指定记录 #

如果每组只需要保留某一条特定记录（比如DEPT_NUM最小的、最新的，或者任意一条），可以用ROW_NUMBER()窗口函数给每组记录编号，再筛选编号为1的行：

WITH grouped_data AS (
    SELECT 
        rts.MERCH_DIV_NUM,
        rts.DEPT_NUM,
        bc.subject_code,
        bc.category_code,
        rts.ASSORTMENT_CODE,
        rts.CURR_PEND_QTY,
        rts.CURR_PEND_AMT,
        a.assortment_desc,
        -- 按目标分组列分区，ORDER BY指定每组内选记录的规则，比如按DEPT_NUM升序取最小的那条
        ROW_NUMBER() OVER (PARTITION BY bc.subject_code, bc.category_code, rts.ASSORTMENT_CODE ORDER BY rts.DEPT_NUM) AS rn
    FROM rts_subcat_dept_pending rts
    INNER JOIN bn_assortment a ON rts.assortment_code = a.assortment_code
    INNER JOIN bn_category bc ON SUBSTR(rts.assortment_code, 0, 4) = bc.category_code
)
SELECT 
    MERCH_DIV_NUM,
    DEPT_NUM,
    subject_code,
    category_code,
    ASSORTMENT_CODE,
    CURR_PEND_QTY,
    CURR_PEND_AMT,
    assortment_desc
FROM grouped_data
WHERE rn = 1;

• 注意：如果每组内有多个记录符合排序规则（比如DEPT_NUM相同），可以换成RANK()或者DENSE_RANK()，根据业务需求选择。

方法2：对非分组列使用聚合函数 #

如果非分组列在每组内有明确的聚合逻辑（比如求和、取最大/最小值），直接给这些列套聚合函数即可，就像你对DEPT_NUM用min()一样：

SELECT 
    -- 按业务需求选聚合函数，比如取MERCH_DIV_NUM的最小值
    MIN(rts.MERCH_DIV_NUM) AS MERCH_DIV_NUM,
    MIN(rts.DEPT_NUM) AS DEPT_NUM,
    bc.subject_code,
    bc.category_code,
    rts.ASSORTMENT_CODE,
    -- 比如对 pending 数量和金额求和，适合需要汇总的场景
    SUM(rts.CURR_PEND_QTY) AS CURR_PEND_QTY,
    SUM(rts.CURR_PEND_AMT) AS CURR_PEND_AMT,
    -- 因为assortment_code和assortment_desc是一一对应的，用min/max都能拿到正确值
    MIN(a.assortment_desc) AS assortment_desc
FROM rts_subcat_dept_pending rts
INNER JOIN bn_assortment a ON rts.assortment_code = a.assortment_code
INNER JOIN bn_category bc ON SUBSTR(rts.assortment_code, 0, 4) = bc.category_code      
GROUP BY bc.subject_code, bc.category_code, rts.ASSORTMENT_CODE;

• 这种方法的核心是：确保聚合逻辑符合业务需求，比如如果CURR_PEND_QTY是每组需要累加的，就用SUM；如果只需要每组任意一条的数值，用MIN/MAX都可以。

方法3：关联子查询获取分组对应记录 #

先提取唯一的分组列组合，再关联回原表和其他表，筛选每组内的特定记录：

SELECT 
    rts.MERCH_DIV_NUM,
    rts.DEPT_NUM,
    bc.subject_code,
    bc.category_code,
    rts.ASSORTMENT_CODE,
    rts.CURR_PEND_QTY,
    rts.CURR_PEND_AMT,
    a.assortment_desc
FROM (
    -- 先拿到所有唯一的分组列组合
    SELECT DISTINCT bc.subject_code, bc.category_code, rts.ASSORTMENT_CODE
    FROM rts_subcat_dept_pending rts
    INNER JOIN bn_category bc ON SUBSTR(rts.assortment_code, 0, 4) = bc.category_code
) AS groups
-- 关联回原表，这里加条件取每组内DEPT_NUM最小的记录
INNER JOIN rts_subcat_dept_pending rts 
    ON groups.ASSORTMENT_CODE = rts.ASSORTMENT_CODE
    AND bc.subject_code = groups.subject_code
    AND bc.category_code = groups.category_code
    AND rts.DEPT_NUM = (SELECT MIN(rts2.DEPT_NUM) FROM rts_subcat_dept_pending rts2 WHERE rts2.ASSORTMENT_CODE = rts.ASSORTMENT_CODE)
INNER JOIN bn_assortment a ON rts.assortment_code = a.assortment_code
INNER JOIN bn_category bc ON SUBSTR(rts.assortment_code, 0, 4) = bc.category_code;

• 这种写法相对繁琐，适合窗口函数不支持的旧版本数据库，或者复杂关联场景。

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