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

最新推荐文章于 2024-03-28 17:24:22 发布

SAPmatinal

最新推荐文章于 2024-03-28 17:24:22 发布

阅读量3.7k

点赞数 2

分类专栏： HANA进阶学习文章标签： HANA

本文链接：https://blog.csdn.net/sapmatinal/article/details/71304825

版权

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

191 篇文章 41 订阅

订阅专栏

简单举例

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 ;