在下面的查询中,我试图按照计算最高的WorstDerogLevel和最低的RecentDerogMonths对行进行排序,但它将所有行都排序为1。
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'这是我的结果集

发布于 2017-12-19 16:11:09
只需通过添加两列PartitionField和OrderField以及您在RANK() OVER上的表达式来调试查询
有两种情况,每一行都有RANK = 1
https://stackoverflow.com/questions/47890795
复制相似问题