問題描述

我有下一張桌子:

假設這些用戶根據他們插入的日期按降序排序.

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.

這篇關于用于更改不同用戶的多個值的 SQL Server 循環的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！