删除重复值而不区分并将其更改为NULL值

Question

我想删除重复的行，并将重复的行转换为空值，但我不知道如何在SQL Server

这是我的表记录的片段

<表类> RefNo TrxDate TrxID tbody><<tr>12386888462021-05-26 18:35:54.393' 2 f01b431-bca5-4d84-cd90-d5a8727a782d12386888462021-05-26 18:33:48.993' 2 f01b431-bca5-4d84-cd90-d5a8727a782d

在ROW_NUMBER中使用一些CASE表达式。PARTITION BY子句是一个猜测，因为我们有最小的预期结果和没有样本数据(所以我不能测试)，所以你可能需要适当地修改它:

SELECT 
CASE ROW_NUMBER() OVER (PARTITION BY Y.TransactionID ORDER BY X.TransactionDate DESC) WHEN 1 THEN X.ReferenceNo END AS ReferenceNo,
CASE ROW_NUMBER() OVER (PARTITION BY Y.TransactionID ORDER BY X.TransactionDate DESC) WHEN 1 THEN X.TransactionDate END AS TransactionDate,
CASE ROW_NUMBER() OVER (PARTITION BY Y.TransactionID ORDER BY X.TransactionDate DESC) WHEN 1 THEN Y.TransactionID END AS TransactionID
FROM [TABLE] X
INNER JOIN [TABLE] Y ON X.Key1  = X.Key1 
AND RIGHT(Y.Key2,10) = RIGHT(X.Key2,10)
AND Y.Key3 = 'Blablabla'
AND X.Key4 <> Y.Key4;

或者您可以只使用一次CTE和row_number()(因为我不喜欢在查询中重复表达式)，使用case表达式来清空不需要的值。

WITH CTE AS (
SELECT 
X.ReferenceNo
, X.TransactionDate
, Y.TransactionID
, ROW_NUMBER() OVER (PARTITION BY X.ReferenceNo ORDER BY X.TransactionDate DESC) rn
FROM [TABLE] X
INNER JOIN [TABLE] Y ON X.Key1  = X.Key1 
AND RIGHT(Y.Key2,10) = RIGHT(X.Key2,10)
AND Y.Key3 = 'Blablabla'
AND X.Key4 <> Y.Key4
)
SELECT
CASE WHEN rn > 1 THEN NULL ELSE ReferenceNo END ReferenceNo
, CASE WHEN rn > 1 THEN NULL ELSE TransactionDate END TransactionDate
, CASE WHEN rn > 1 THEN NULL ELSE TransactionID END TransactionID
FROM CTE;