时间戳

首页 > 解决方案 > 使用 case 语句并为每条前 10 条记录设置不同的值

sql - 使用 case 语句并为每条前 10 条记录设置不同的值

问题描述

我有一个表，它返回包含两列的记录列表：

SELECT Id, Duration from dbo.[VW_ActivityDuration] ORDER BY DURATION desc

我想使用该case语句并为每前 10 条记录设置不同的值，按DURATION降序排列。

例子：

set the value as 5 for the first 10 records 

and set the value as 4 for the next 10 records 

and set the value as 3 for the other next 10 records 

and set the value as 2 for the other next 10 records

同样，在那之后我必须为 all 设置值 1

SELECT TOP (1000) [Id]
      ,[Name],

      CASE
        WHEN  ---  THEN '5'
        ELSE '1'
    END AS [Value]

  FROM [EngagementDb].[dbo].[VW_ActivityDuration]
  ORDER BY [DurationMinutes] desc

任何先机、提示或建议将不胜感激。我无法开始编写查询来解决这个问题。

标签： sqlsql-serversql-order-bycasewindow-functions

解决方案

我想你想要row_number()一个case表达：

select id, DurationMinutes,
    case 
        when row_number() over(order by DurationMinutes desc) <= 10 then 5
        when row_number() over(order by DurationMinutes desc) <= 20 then 4
        when row_number() over(order by DurationMinutes desc) <= 30 then 3
        when row_number() over(order by DurationMinutes desc) <= 40 then 2
        else 1
    end as val
from [EngagementDb].[dbo].[VW_ActivityDuration]
order by DurationMinutes desc

我们可以用算术把它缩短一点：

select id, DurationMinutes,
    case when row_number() over(order by DurationMinutes desc) <= 40 
        then 5 - (row_number() over(order by DurationMinutes desc) - 1) / 10
        else 1
    end as val
from [EngagementDb].[dbo].[VW_ActivityDuration]
order by DurationMinutes desc

推荐阅读