当同一表的另一列的多条记录匹配时，更新列

Question

我有如下表：

Input table:
RequestNumber            TrackName         Date
-----------------------------------------------------------
02209                    Middle         2017-05-28 00:00:00
0263                     Middle         2017-06-29 00:00:00
0633                     Middle         2017-09-10 00:00:00
0762                     Back           2017-06-23 00:00:00
0762                     Front          2017-06-23 00:00:00
0762                     Middle         2017-06-23 00:00:00
01839                    Middle         2017-03-09 00:00:00

我需要检查"跟踪名称"的"请求编号"和"日期"是否相同。 如果"RequestNumber"和"Date"的多个匹配，那么我应该将"TrackName"列更新为"所有三个"，如下所示 输出示例(在此示例中有 3 条记录匹配)

Output table:
RequestNumber            TrackName         Date
-----------------------------------------------------------
02209                    Middle         2017-05-28 00:00:00
0263                     Middle         2017-06-29 00:00:00
0633                     Middle         2017-09-10 00:00:00
0762                     All three      2017-06-23 00:00:00
01839                    Middle         2017-03-09 00:00:00

为了获得上述设计输出，这是我尝试过的SQL。Howveer，它将所有曲目名称更新为所有三个。

UPDATE a 
SET a.[TrackName] = 'All three'
FROM Table1 as a
INNER JOIN 
(SELECT [RequestNumber], row_number() OVER (ORDER BY [RequestNumber] DESC) as rowNumber
FROM Table1 ) drRowNumbers ON drRowNumbers.[RequestNumber] = a.[RequestNumber] and drRowNumbers.[Date] = a.[Date]

希望我正确解释了这一点。我在做什么？有什么疑问可以解决这个问题吗？

注意：记录是动态的，因此无法进行硬编码(如果有)。

谢谢。

Answer 1

试试这个。这将返回"所有 3"而不是"所有三个"-

SELECT RequestNumber,
CASE 
WHEN COUNT(*) = 1 THEN MAX(TrackName) 
ELSE 'All ' + CAST( COUNT(*) AS VARCHAR) 
END TrackName,
Date
FROM your_table
GROUP BY RequestNumber,Date

Answer 2

我想你想要SELECT语句：

SELECT t1.RequestNumber, (CASE WHEN COUNT(DISTINCT t1.TrackName) = t2.TrackNo 
THEN CONCAT('All ', t2.TrackNo)  
ELSE MIN(t1.TrackName) 
END) AS TrackName, t1.Date
FROM table1 t1 CROSS JOIN
(SELECT COUNT(DISTINCT TrackName) AS TrackNo FROM table1) AS t2
GROUP BY t1.RequestNumber, t1.Date;

Answer 3

这不仅仅是一个更新，也是一个删除。
这意味着您必须有一个两步过程 - 一个用于更新相关记录，另一个用于删除第一步创建的重复项。

这可以使用几个使用窗口函数(如count() over()和row_number() over())的公用表表达式来完成，当这两个步骤都连接在事务中时。

首先，创建并填充示例表(请在以后的问题中保存此步骤)：

DECLARE @T AS TABLE
(
RequestNumber int,
TrackName varchar(10),
[Date] datetime2
);
INSERT INTO @T (RequestNumber, TrackName, Date) VALUES
(02209, 'Middle', '2017-05-28 00:00:00'),
(0263, 'Middle', '2017-06-29 00:00:00'),
(0633, 'Middle', '2017-09-10 00:00:00'),
(0762, 'Back', '2017-06-23 00:00:00'),
(0762, 'Front', '2017-06-23 00:00:00'),
(0762, 'Middle', '2017-06-23 00:00:00'),
(01839, 'Middle', '2017-03-09 00:00:00');

接下来，启动一个try块和一个交易：

BEGIN TRY
BEGIN TRANSACTION;

然后，识别并更新相关记录：

WITH CTE AS
(
SELECT  RequestNumber, 
TrackName, 
Date,
COUNT(TrackName) OVER(PARTITION BY RequestNumber, Date) As Cnt
FROM @T
)
UPDATE CTE
SET TrackName = 'All Three'
WHERE Cnt = 3;

接下来，删除重复项：

WITH CTE AS
(
SELECT  RequestNumber, 
TrackName, 
Date,
ROW_NUMBER() OVER(PARTITION BY RequestNumber, Date ORDER BY TrackName) As Rn
FROM @T
)
DELETE 
FROM CTE 
WHERE Rn > 1;

提交事务并关闭try块：

COMMIT TRANSACTION;
END TRY

使用 catch 块回滚事务：

BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
-- you might want to print the result of ERROR_MESSAGE() here...
END CATCH

最后，选择以查看更改：

SELECT  RequestNumber, 
TrackName, 
Date
FROM @T

结果：

RequestNumber   TrackName   Date
2209            Middle      28.05.2017 00:00:00
263             Middle      29.06.2017 00:00:00
633             Middle      10.09.2017 00:00:00
762             All Three   23.06.2017 00:00:00
1839            Middle      09.03.2017 00:00:00

您可以在 rextester 上看到现场演示(减去不允许的交易部分和尝试......无论如何，没有交易就无关紧要的捕获)

Answer 4

试一试

步骤 1 将其中一个记录设置为所有三个

update table1
set TrackName = 'All Three'
where requestnumber in (select requestnumber
from table1
group by requestnumber,[date]
having count(*) = 3)
and trackname = 'Front'

步骤 2 删除不再需要的数据

delete table1
where requestnumber in (select requestnumber
from table1
group by requestnumber,[date]
having count(*) = 3)
and trackname <> 'All Three'