SQL 查询中的复杂排序：可能使用 CASE WHEN

Question

我有一个复杂的优先级算法，我想在返回优先级数据页时用 SQL 编写该算法。

其中一些根据固定值确定优先级，另一些则根据变量值排序。即

// Initial sort on ATTR1 (value1/value2 is higher than value3)
if ATTR1 = value1 or value2
  then orderBy creationDate, then modifiedDate, then author
else if ATTR1 = value3
  then 
    // if ATTR1 = value3, do secondary sort on ATTR2 (value4 is higher than value5)
    if ATTR2 = value4
       then orderBy oldPriority, then modifiedDate, then creationDate, then author
    if ATTR2 = value5
       then orderBy creationDate, then modifiedDate

我已经查看了 SQL CASE WHEN，但不确定如何使其与第二级属性比较一起工作。

CASE WHEN 是实现这一目标的合适工具吗？有人对处理额外的复杂性有什么建议吗？

先感谢您！

Answer 1

像这样的事情应该做到这一点：

ORDER BY attr1,
CASE WHEN ATTR1 IN (value1, value2)
       THEN TO_CHAR(creationDate,'YYYYMMDDHH24MISS') 
            || to_char(modifiedDate,'YYYYMMDDHH24MISS') || author
     WHEN ATTR1 = value3
       THEN attr2 || CASE WHEN ATTR2 = value4
                            THEN to_char(oldPriority,'99999')
                                 || to_char(modifiedDate,'YYYYMMDDHH24MISS')
                                 || to_char(creationDate,'YYYYMMDDHH24MISS') 
                                 || author
                          WHEN ATTR2 = value5
                            THEN to_char(creationDate,'YYYYMMDDHH24MISS')
                                 || to_char(modifiedDate,'YYYYMMDDHH24MISS')
                          END
     END

我假设 oldPriority 是一个 NUMBER 列，但情况可能并非如此。关键是使用适当的格式掩码将所有内容转换为顺序正确的字符串。

Answer 2

做出一些小假设，看起来这应该可行：

order by
   case ATTR1
      when value1 then
         1
      when value2 then
         2
      when value3 then
         3
   end,
      when ATTR1 in (value1,value2) 
           or (ATTR1 = value3 and ATTR2 = value5) then
         creationDate
      else
         null
      end,
   case 
      when ATTR1 = value3 and ATTR2 = value4 then
         oldPriority
      else
         null
      end,
   modifiedDate,
   case
      when not (ATTR1 = value3 and ATTR2 = value5) then
          author
      else null
      end