如何正确按日期排序？Close Date排序无序问题的技术求助

尝试按Close Date列**由近及远（降序）**排序交易，但结果不符合预期。例如当前排序结果为：2022年10月31日、2024年5月31日、2023年5月31日、2023年3月31日、2023年7月31日、2023年1月31日、2022年12月31日，明显不符合日期降序的逻辑顺序。

当前使用的SQL代码如下：

select D.ROWID,
    '<b style="font-size: large; color:#DC4300;">' || D.CUSTOMER || '</b><br>' ||
    '<b>Project Type: </b>' || D.PROJECT || '<br>' ||
    '<b>Engagement Type: </b>' || NVL(D.ENGAGEMENT, 'Not Specified') || '<br>' ||
    '<b>Opportunity ID: </b>' || NVL(D.OPP_ID, 'Not Specified') || '<br>' ||
    --'<b>SF Rep: </b>' || com.COMMERCIAL_MANAGER || '<br>' ||
    '<b>CPR: </b>' || D.CPR || '<br>' ||
    '<b>VP: </b>' || NVL(D.VP, 'N/A') || '<br>' ||
    '<b>Country: </b>' || c.COUNTRY as ENGAGEMENT,
    D.CUSTOMER,
    com.COMMERCIAL_MANAGER,
    D.ARR,
    case D.LICENSE
        when null then 0
        else D.LICENSE
    end as LICENSE,
    D.SALES_STAGE,
    NVL(to_char(D.CLOSE_DATE, 'ddth Month YYYY'), 'Not Specified') as CLOSING_DATE,
    '<b>Deal Summary: </b>' || D.COMPLEX_DEMANDS || '<br>' ||
    '<b>Approval Date: </b>' || NVL(to_char(D.APPROVALS_DATE, 'ddth Month YYYY'), 'Not Specified') || '<br>' ||
    '<b>Drafting Date: </b>' || NVL(to_char(D.DRAFTING_DATE, 'ddth Month YYYY'), 'Not Specified') || '<br><br>' ||
    --'<b style="color: #DC4300;">Closing Date: </b>' || NVL(to_char(D.CLOSE_DATE, 'ddth Month YYYY'), 'Not Specified') || '<br><br>' ||
    '<b>Customer Facing: </b>' || NVL(D.CUSTOMER_FACING, 'Not Specified') || '<br>' ||
    '<b>Tier 3 & 1 Engaged: </b>' || D.TIER3_1 || '<br>' ||
    '<b>Closure Risk(s): </b>' || NVL(D.CLOSURE_RISK, 'None') || '<br><br>' ||
    '<b>Challenge(s): </b>' || NVL(D.CHALLENGES, 'None') as DEAL_DETAILS,
    NVL(D.FBE, 'N/A'),
    D.NEXT_STEPS || '<br>' || 
    '<b>Contract Standpoint: </b>' || NVL(D.CONTRACT_STANDPOINT, 'N/A')|| '<br>' ||
    '<b>Main Contact: </b>' || D.CUSTOMER_CONTACT_NAME || ' - ' || D.CUSTOMER_CONTACT_ROLE as NEXT_STEPS
   
from TECHCOM D, 
    COUNTRY_LOOKUP c, 
    COMMERCIAL_MANAGER_LOOKUP com
where D.country_ID = c.country_ID
    and D.COMMERCIAL_MANAGER_ID = com.COMMERCIAL_MANAGER_ID
    and D.ARR is not null
    and not(D.SALES_STAGE in ('Won', 'Lost'))
ORDER BY D.ARR desc

解决方案 #

问题根源有两点：

1. 当前代码的排序依据是D.ARR desc，并非日期列，完全没按Close Date排序；
2. 即使误用转换后的CLOSING_DATE字符串排序，也会因为字符串的字符顺序和日期逻辑顺序不一致导致错误。

修改方案：将ORDER BY子句替换为按原始日期字段D.CLOSE_DATE降序排序，同时可根据需求处理NULL值（比如将无Close Date的记录排到最后）：

select D.ROWID,
    '<b style="font-size: large; color:#DC4300;">' || D.CUSTOMER || '</b><br>' ||
    '<b>Project Type: </b>' || D.PROJECT || '<br>' ||
    '<b>Engagement Type: </b>' || NVL(D.ENGAGEMENT, 'Not Specified') || '<br>' ||
    '<b>Opportunity ID: </b>' || NVL(D.OPP_ID, 'Not Specified') || '<br>' ||
    --'<b>SF Rep: </b>' || com.COMMERCIAL_MANAGER || '<br>' ||
    '<b>CPR: </b>' || D.CPR || '<br>' ||
    '<b>VP: </b>' || NVL(D.VP, 'N/A') || '<br>' ||
    '<b>Country: </b>' || c.COUNTRY as ENGAGEMENT,
    D.CUSTOMER,
    com.COMMERCIAL_MANAGER,
    D.ARR,
    case D.LICENSE
        when null then 0
        else D.LICENSE
    end as LICENSE,
    D.SALES_STAGE,
    NVL(to_char(D.CLOSE_DATE, 'ddth Month YYYY'), 'Not Specified') as CLOSING_DATE,
    '<b>Deal Summary: </b>' || D.COMPLEX_DEMANDS || '<br>' ||
    '<b>Approval Date: </b>' || NVL(to_char(D.APPROVALS_DATE, 'ddth Month YYYY'), 'Not Specified') || '<br>' ||
    '<b>Drafting Date: </b>' || NVL(to_char(D.DRAFTING_DATE, 'ddth Month YYYY'), 'Not Specified') || '<br><br>' ||
    --'<b style="color: #DC4300;">Closing Date: </b>' || NVL(to_char(D.CLOSE_DATE, 'ddth Month YYYY'), 'Not Specified') || '<br><br>' ||
    '<b>Customer Facing: </b>' || NVL(D.CUSTOMER_FACING, 'Not Specified') || '<br>' ||
    '<b>Tier 3 & 1 Engaged: </b>' || D.TIER3_1 || '<br>' ||
    '<b>Closure Risk(s): </b>' || NVL(D.CLOSURE_RISK, 'None') || '<br><br>' ||
    '<b>Challenge(s): </b>' || NVL(D.CHALLENGES, 'None') as DEAL_DETAILS,
    NVL(D.FBE, 'N/A'),
    D.NEXT_STEPS || '<br>' || 
    '<b>Contract Standpoint: </b>' || NVL(D.CONTRACT_STANDPOINT, 'N/A')|| '<br>' ||
    '<b>Main Contact: </b>' || D.CUSTOMER_CONTACT_NAME || ' - ' || D.CUSTOMER_CONTACT_ROLE as NEXT_STEPS
   
from TECHCOM D, 
    COUNTRY_LOOKUP c, 
    COMMERCIAL_MANAGER_LOOKUP com
where D.country_ID = c.country_ID
    and D.COMMERCIAL_MANAGER_ID = com.COMMERCIAL_MANAGER_ID
    and D.ARR is not null
    and not(D.SALES_STAGE in ('Won', 'Lost'))
-- 修改排序逻辑：按Close Date降序，NULL值排最后
ORDER BY D.CLOSE_DATE desc NULLS LAST

说明：NULLS LAST用于将没有Close Date的记录排在所有有日期的记录之后，若需要排到最前可改为NULLS FIRST，根据业务需求调整即可。

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