ORDER BY 与 Case-Statement DESC [英] ORDER BY with Case-Statement DESC

问题描述

• 如何使用 CASE 语句ORDER BY
  • 第一组:日期列Col1中的空值按日期列Col2 DESC
  • 第二组:按Col1 DESC
  排序的日期列中的非空值-Col1
  • How to ORDER BY with a CASE-Statement
    • first group: null values in date-column Col1 sorted by date-column Col2 DESC
    • second group: not-null values in date-column-Col1 sorted by Col1 DESC

    我尝试了以下操作:

    SELECT columns FROM tables WHERE condition
    ORDER BY 
        case when Table1.Col1 IS NULL     then 0 end, Table2.Col2 DESC,
        case when Table1.Col1 IS NOT NULL then 1 end, Table1.Col1 DESC
    

    但是排序顺序不对，NOT NULL 值在前(按 Col2 而不是 Col1 排序).我想我错过了一个细节.

    But the sort order is wrong, the NOT NULL values are first(sorted by Col2 instead of Col1). I think i've missed a detail.

    推荐答案

    SELECT columns FROM tables 
    WHERE condition 
    ORDER BY      
       case when Table1.Col1 IS NULL then 0 else 1 end ASC      
       ,case when Table1.Col1 IS NULL then Table2.Col2 else Table1.Col1 end DESC
    

    这篇关于ORDER BY 与 Case-Statement DESC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！