SAP HANA SQL GROUP BY / ORDER BY / OVER / CASE

原创于 2017-05-06 21:51:59 发布 · 4k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#HANA

HANA进阶学习专栏收录该内容

194 篇文章

订阅专栏

本文介绍了一种使用SQL解决复杂条件排序的方法，通过LEAD窗口函数与CASE WHEN语句结合，实现了根据不同条件对数据进行灵活排序的功能。具体包括如何判断当前记录与下一条记录之间的差异，并据此调整排序依据。

简单举例

select name, score1, score2, nextscore1,
      case 
           when ((nextscore1 IS NOT NULL) AND (score1 - nextscore1 < 0.1))
           then score2 
           else score1 
      end score_sort1,
      case when (nextscore1 IS NOT NULL) AND (score1 - nextscore1 < 0.1) 
           then score1 
           else score2 
      end score_sort2
from
    (select 
          name,
          score1,
          score2,
          LEAD(score1, 1) over (order by score1 desc, score2 desc) as     nextscore1
    from
        scores
    group by
          name,
          score1,
          score2)
ORDER BY 
      case 
           when ((nextscore1 IS NOT NULL) AND (score1 - nextscore1 < 0.1))
           then score2 
           else score1 
      end ,  
      case when (nextscore1 IS NOT NULL) AND (score1 - nextscore1 < 0.1) 
           then score1 
           else score2 
      end ;