Oracle中使用DISTINCT后仍无法获取唯一值的问题

为什么DISTINCT没生效？ #

你当前的SELECT语句包含了ne_id、latitude、longitude这类列，哪怕SAP_ID相同，只要这些列的值有差异，DISTINCT就会把它们当成不同行保留——因为DISTINCT是基于所有返回列的组合去重，不是只针对SAP_ID。

ROW_NUMBER函数的使用问题 #

你只是把rn作为列返回，但没筛选只保留rn=1的行，所以还是会返回所有符合条件的行。另外，ORDER BY SAP_ID desc在PARTITION BY SAP_ID的分区里没有意义，同一分区内的SAP_ID完全相同，排序后无法区分行的优先级，rn的分配会是随机的1、2、3...

修正后的查询语句 #

用子查询先处理运营商映射并生成行号，再在外层筛选每个SAP_ID对应的唯一行（如果需要保留特定规则的行，调整ORDER BY后的字段即可，比如按ne_id或业务上的优先级字段排序）：

SELECT sap_id, OPERATORNAME, VENDOR_CODE, rj_company_code_1, city_name, ne_id, latitude, longitude
FROM (
    SELECT 
        sap_id, 
        CASE
            WHEN operatorname IN('VIOM', 'idea', 'ATC', 'Vodafone', 'IDEA') THEN
                CAST('ATC TELECOM INFRASTRUCTURE PVT LTD' AS NVARCHAR2(50))
            WHEN operatorname IN('GTL', 'CNIL') THEN 
                CAST('GTL INFRASTRUCTURE LTD' AS NVARCHAR2(50))
            WHEN operatorname IN('Ascend') THEN 
                CAST('ASCEND TELECOM INFRASTRUCTURE' AS NVARCHAR2(50))
            WHEN operatorname IN('Tower Vision (TVIPL)') THEN
                CAST('TOWER VISION INDIA PVT LTD' AS NVARCHAR2(50))
            WHEN operatorname IN('INDUS') THEN 
                CAST('INDUS TOWERS LTD' AS NVARCHAR2(50))
            WHEN operatorname IN('Bharti Infratel') THEN
                CAST('BHARTI INFRATEL LIMITED' AS NVARCHAR2(50))
            ELSE operatorname
        END OPERATORNAME,
        CASE
            WHEN operatorname IN('VIOM', 'idea', 'ATC', 'Vodafone', 'IDEA') THEN '3150909'
            WHEN operatorname IN('GTL', 'CNIL') THEN '3164892'
            WHEN operatorname IN('Ascend') THEN '3155709'
            WHEN operatorname IN('Tower Vision (TVIPL)') THEN '3157870'
            WHEN operatorname IN('INDUS') THEN '3578944'
            WHEN operatorname IN('Bharti Infratel') THEN '3147912'
            ELSE ''
        END VENDOR_CODE,
        rj_company_code_1,
        city_name,
        ne_id,                            
        latitude,
        longitude,
        ROW_NUMBER() OVER (PARTITION BY SAP_ID ORDER BY ne_id) AS rn -- 替换为你需要的排序字段
    FROM r4g_osp.enodeb
    WHERE priority_site = 'IP1'
        AND scope IN('EnodeB-Connected_Fibre', 'EnodeB-Connected_MW')
        AND jcpstatus = 'On Air'                            
        AND sap_id IN (SELECT SAP_ID FROM TEMP_IPCOLO_BILLING_MST)
) t
WHERE rn = 1;

额外优化建议 #

你的CASE语句重复判断了相同的operatorname条件，可以用CTE合并重复逻辑，减少计算量：

WITH mapped_data AS (
    SELECT 
        sap_id,
        operatorname,
        rj_company_code_1,
        city_name,
        ne_id,                            
        latitude,
        longitude,
        CASE
            WHEN operatorname IN('VIOM', 'idea', 'ATC', 'Vodafone', 'IDEA') THEN
                CAST('ATC TELECOM INFRASTRUCTURE PVT LTD' AS NVARCHAR2(50))
            WHEN operatorname IN('GTL', 'CNIL') THEN 
                CAST('GTL INFRASTRUCTURE LTD' AS NVARCHAR2(50))
            WHEN operatorname IN('Ascend') THEN 
                CAST('ASCEND TELECOM INFRASTRUCTURE' AS NVARCHAR2(50))
            WHEN operatorname IN('Tower Vision (TVIPL)') THEN
                CAST('TOWER VISION INDIA PVT LTD' AS NVARCHAR2(50))
            WHEN operatorname IN('INDUS') THEN 
                CAST('INDUS TOWERS LTD' AS NVARCHAR2(50))
            WHEN operatorname IN('Bharti Infratel') THEN
                CAST('BHARTI INFRATEL LIMITED' AS NVARCHAR2(50))
            ELSE operatorname
        END mapped_operator,
        CASE
            WHEN operatorname IN('VIOM', 'idea', 'ATC', 'Vodafone', 'IDEA') THEN '3150909'
            WHEN operatorname IN('GTL', 'CNIL') THEN '3164892'
            WHEN operatorname IN('Ascend') THEN '3155709'
            WHEN operatorname IN('Tower Vision (TVIPL)') THEN '3157870'
            WHEN operatorname IN('INDUS') THEN '3578944'
            WHEN operatorname IN('Bharti Infratel') THEN '3147912'
            ELSE ''
        END vendor_code
    FROM r4g_osp.enodeb
    WHERE priority_site = 'IP1'
        AND scope IN('EnodeB-Connected_Fibre', 'EnodeB-Connected_MW')
        AND jcpstatus = 'On Air'                            
        AND sap_id IN (SELECT SAP_ID FROM TEMP_IPCOLO_BILLING_MST)
)
SELECT sap_id, mapped_operator AS OPERATORNAME, vendor_code, rj_company_code_1, city_name, ne_id, latitude, longitude
FROM (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY SAP_ID ORDER BY ne_id) AS rn
    FROM mapped_data
) t
WHERE rn = 1;

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