用于更改不同用户的多个值的 SQL Server 循环

2021-09-10 00:00:00 database tsql sql-server

我有下一张桌子:

假设这些用户根据他们插入的日期按降序排序.

Supposing these users are sort in descending order based on their inserted date.

现在，我想做的是改变他们的排序编号，对于每个用户，排序编号必须从 1 开始，直到每个用户的出现次数.结果应该类似于:

Now, what I want to do, is to change their sorting numbers in that way that for each user, the sorting number has to start from 1 up to the number of appearances of each user. The result should look something like:

有人可以给我提供一些如何在 sql server 中执行此操作的线索吗?谢谢.

Can someone provide me some clues of how to do it in sql server ? Thanks.

推荐答案

您可以使用 ROW_NUMBER 排名函数，用于计算给定分区和顺序的行的排名.

You can use the ROW_NUMBER ranking function to calculate a row's rank given a partition and order.

在这种情况下，您要计算每个用户 PARTITION BY User_ID 的行号.所需的输出显示按 ID 排序就足够了 ORDER BY ID.

In this case, you want to calculate row numbers for each user PARTITION BY User_ID. The desired output shows that ordering by ID is enough ORDER BY ID. 

SELECT 
    Id, 
    User_ID, 
    ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY Id) AS Sort_Number
FROM MyTable

您还可以使用其他排名函数，例如 RANK、DENSE_RANK 根据分数计算排名，或 NTILE 计算每行的百分位数.

There are other ranking functions you can use, eg RANK, DENSE_RANK to calculate a rank according to a score, or NTILE to calculate percentiles for each row.

您还可以使用带有聚合的 OVER 子句来创建运行总计或移动平均值，例如 SUM(Id) OVER (PARTITION BY User_ID ORDER BY Id) 将创建每个用户的 Id 值的运行总数.

You can also use the OVER clause with aggragets to create running totals or moving averages, eg SUM(Id) OVER (PARTITION BY User_ID ORDER BY Id) will create a running total of the Id values for each user.

相关文章