使用DENSE_RANK排除空值 [英] Exclude null values using DENSE_RANK

问题描述

Dense_Rank正在考虑所有因素.有没有一种方法可以排除空值，因此1之后的下一个等级将是2而不是3.

Dense_Rank is taking everything into account. Is there a way to exclude the null values so the next rank after 1 would be 2 and not 3.

这是表格现在的样子:

 A     | DENSE_R 
 --------------
 1     | 1  
 --------------
 2     | null  
 --------------
 3     | 3 
 --------------
 4     |  4    

这就是我希望表格看起来像的样子:

This is what I want the table to look like:

 A     | DENSE_R 
 --------------
 1     | 1  
 --------------
 2     | null  
 --------------
 3     | 2 
 --------------
 4     |  3  

我正在使用以下代码来这样做:-

I'm using the following code to do so:-

WITH CTE AS
(
 SELECT A 
 FROM A1
)
SELECT A,
CASE 
  WHEN  **Condition**
  THEN DENSE_RANK() OVER (Order by [A] ASC)
END
AS 'DENSE_R'
FROM CTE

推荐答案

使用与您已经使用过的**Condition**相同的**Condition**.

Use partition by the same **Condition** as you used already.

WITH CTE AS
(
 SELECT A 
 FROM A1
)
SELECT A,
CASE 
  WHEN  **Condition**
  THEN DENSE_RANK() OVER (Partition by (case when **Condition** then 1 else 0 end) Order by [A] ASC)
END
AS 'DENSE_R'
FROM CTE

这篇关于使用DENSE_RANK排除空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！