首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >为什么这个sql秩OVER()命令给了我所有的等级1

问为什么这个sql秩OVER()命令给了我所有的等级1
EN

Stack Overflow用户
提问于 2017-12-19 16:02:52
回答 1查看 285关注 0票数 0

在下面的查询中,我试图按照计算最高的WorstDerogLevel和最低的RecentDerogMonths对行进行排序,但它将所有行都排序为1。

代码语言:javascript
复制
SELECT 
            strTransID,
            strRateCode as PreviousHighRateCode,
            strRateStatusCode as TradelineRateCode,
            dateReported,
            datePreviousHigh,
            CASE
                WHEN 
                    strRateStatusCode IN ('2', '3', '4', '5', '7', '8', '9', 'F', 'G', 'H', 'M', 'Z', '#', '$')
                    AND
                    (
                        strRateStatusCode IN ('F', 'G', 'H', 'M', 'Z', '#', '$')
                        OR strRateStatusCode >= strRateCode 
                    )
                THEN 
                    strRateStatusCode 
                ELSE 
                    strRateCode 
            END AS WorstDerogLevel
            ,
                DateDiff(month, 
                    CASE 
                        WHEN 
                            ( 
                                dateReported >= datePreviousHigh 
                                OR datePreviousHigh is null
                            )
                            AND strRateStatusCode IN ('2', '3', '4', '5', '7', '8', '9', 'F', 'G', 'H', 'M', 'Z', '#', '$')
                            AND
                            (
                                strRateStatusCode IN ('F', 'G', 'H', 'M', 'Z', '#', '$')
                                OR strRateStatusCode >= strRateCode 
                            )
                        THEN 
                            dateReported 
                        ELSE 
                            datePreviousHigh 
                    END, 
                    dateTransDate) as RecentDerogMonths

            ,

        RANK() OVER   
        (
            PARTITION BY 
                DateDiff(month, 
                    CASE 
                        WHEN 
                            ( 
                                dateReported >= datePreviousHigh 
                                OR datePreviousHigh is null
                            )
                            AND strRateStatusCode IN ('2', '3', '4', '5', '7', '8', '9', 'F', 'G', 'H', 'M', 'Z', '#', '$')
                            AND
                            (
                                strRateStatusCode IN ('F', 'G', 'H', 'M', 'Z', '#', '$')
                                OR strRateStatusCode >= CstrRateCode 
                            )
                        THEN 
                            dateReported 
                        ELSE 
                            datePreviousHigh 
                    END, 
                    dateTransDate) 

            ORDER BY

            CASE
                WHEN 
                    strRateStatusCode IN ('2', '3', '4', '5', '7', '8', '9', 'F', 'G', 'H', 'M', 'Z', '#', '$')
                    AND
                    (
                        strRateStatusCode IN ('F', 'G', 'H', 'M', 'Z', '#', '$')
                        OR strRateStatusCode >= strRateCode 
                    )
                THEN 
                    strRateStatusCode 
                ELSE 
                    strRateCode 
            END desc,

                DateDiff(month, 
                    CASE 
                        WHEN 
                            ( 
                                dateReported >= datePreviousHigh 
                                OR datePreviousHigh is null
                            )
                            AND strRateStatusCode IN ('2', '3', '4', '5', '7', '8', '9', 'F', 'G', 'H', 'M', 'Z', '#', '$')
                            AND
                            (
                                strRateStatusCode IN ('F', 'G', 'H', 'M', 'Z', '#', '$')
                                OR strRateStatusCode >= strRateCode 
                            )
                        THEN 
                            dateReported 
                        ELSE 
                            datePreviousHigh 
                    END, 
                    dateTransDate) asc

        ) AS Rank  

     FROM 
        [Customers]

    WHERE strTransID = '279579407'

这是我的结果集

​

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2017-12-19 16:11:09

只需通过添加两列PartitionField和OrderField以及您在RANK() OVER上的表达式来调试查询

有两种情况,每一行都有RANK = 1

  1. 每一行都属于不同的分区,因此您有大小为1的分区。
  2. 分区中的所有行都有相同的值,都是秩1。
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/47890795

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档