Блог

Рассмотрим, что у меня есть следующая таблица.

 ID  value
1   100
2   200
3   200
5   250
6   1
  

У меня есть следующий запрос, который дает следующий результат. Я хочу исключить значение 200 из функции rank, но все равно эта строка должна быть возвращена.

 SELECT
CASE WHEN Value = 200 THEN 0
     ELSE DENSE_RANK() OVER ( ORDER BY VALUE DESC)
END AS RANK,
ID,
VALUE
FROM @table

RANK   ID   VALUE
1       5   250
0       2   200
0       3   200
4       1   100
5       6   1
  

Но я хочу получить следующий результат. Как этого добиться?

 RANK   ID   VALUE
1       5   250
0       2   200
0       3   200
2       1   100
3       6   1
  

Если столбец VAL не имеет значения null, то с учетом NULL последнее значение в ORDER BY .. DESC

 select  *, dense_rank() over (order by nullif(val,200) desc) * case val when 200 then 0 else 1 end
from myTable
order by val desc;
  

В настоящее время невозможно исключить значение Val в плотном ранге, если вы не фильтруете в предложении where .. по этой причине вы получаете результат ниже

 RANK   ID   VALUE
1       5   250
0       2   200
0       3   200
4       1   100
5       6   1
  

Вам нужно будет отфильтровать один раз, а затем выполнить объединение всех

 ;with cte(id,val)
as
(
select 1,   100  union all
select 2,   200 union all
select 3,   200 union all
select 5,   250 union all
select 6,   1    )
select  *, dense_rank() over (order by val desc) 
 from cte
 where val<>200
union all
 select 0,id,val from cte where val=200
  

Вы можете разделить рейтинг на отдельные запросы для значений, которые вы хотите включить / исключить из рейтинга, и UNION ALL результатов, подобных этому:

Пример автономного исполняемого файла:

 CREATE TABLE #temp ( [ID] INT, [value] INT );

INSERT  INTO #temp
        ( [ID], [value] )
VALUES  ( 1, 100 ),
        ( 2, 200 ),
        ( 3, 200 ),
        ( 5, 250 ),
        ( 6, 1 );

SELECT  *
FROM    ( SELECT    0 RANK ,
                    ID ,
                    value
          FROM      #temp
          WHERE     value = 200  -- set rank to 0 for value = 200
          UNION ALL
          SELECT    DENSE_RANK() OVER ( ORDER BY value DESC ) AS RANK ,
                    ID ,
                    value
          FROM      #temp
          WHERE     value != 200  -- perform ranking on records != 200
        ) t
ORDER BY value DESC ,
        t.ID

DROP TABLE #temp
  

Выдает:

 RANK    ID  value
1       5   250
0       2   200
0       3   200
2       1   100
3       6   1
  

При необходимости вы можете изменить порядок в конце инструкции, я установил его для получения желаемых результатов.

Вы также можете попробовать это тоже:

 SELECT  ISNULL(R, 0) AS Rank ,t.id ,t.value
FROM    tbl1 AS t
    LEFT JOIN ( SELECT  id ,DENSE_RANK() OVER ( ORDER BY value DESC ) AS R
                FROM    dbo.tbl1 WHERE   value <> 200
              ) AS K 
   ON t.id = K.id
ORDER BY t.value DESC
  

Решение в исходном вопросе было на самом деле довольно близким. Простое добавление предложения partition к dense_rank может помочь.

 SELECT    CASE
               WHEN VALUE = 200 THEN 0
               ELSE DENSE_RANK() OVER(
                    PARTITION BY CASE WHEN VALUE = 200 THEN 0 ELSE 1 END 
                    ORDER BY VALUE DESC
               )
          END AS RANK
          ,ID
          ,VALUE
FROM      @table
ORDER BY  VALUE DESC;
  

‘Partition by’ создает отдельные группы для dense_rank таким образом, что порядок выполняется для этих групп по отдельности. По сути, это означает, что вы создаете два ранга одновременно: один для группы без значения 200 и один для группы только со значением 200. Последний должен быть установлен в 0 в «случае, когда».

Пример автономного исполняемого файла:

 DECLARE @table TABLE
(
     ID     INT NOT NULL PRIMARY KEY
     ,VALUE INT     NULL
)

INSERT INTO @table
(
     ID
     ,VALUE
)
SELECT 1, 100
UNION SELECT 2, 200
UNION SELECT 3, 200
UNION SELECT 5, 250
UNION SELECT 6, 1;

SELECT    CASE
               WHEN VALUE = 200 THEN 0
               ELSE DENSE_RANK() OVER(
                    PARTITION BY CASE WHEN VALUE = 200 THEN 0 ELSE 1 END 
                    ORDER BY VALUE DESC
               )
          END AS RANK
          ,ID
          ,VALUE
FROM      @table
ORDER BY  VALUE DESC;
  

 RANK    ID  VALUE
1   5   250
0   2   200
0   3   200
2   1   100
3   6   1