sql - 如何在具有该 ID 多次出现的第一行之后删除具有相同 ID 的其余已排序行?
问题描述
我的表有以下结构DataTable
:每一列都是 int 数据类型,RowID
是一个标识列和主键。LinkID
是一个外键,链接到另一个表的行。
RowID LinkID Order Data DataSpecifier
1 120 1 1 1
2 120 2 1 3
3 120 3 1 10
4 120 4 1 13
5 120 5 1 10
6 120 6 1 13
7 371 1 6 2
8 371 2 3 5
9 371 3 8 1
10 371 4 10 1
11 371 5 7 2
12 371 6 3 3
13 371 7 7 2
14 371 8 17 4
.................................
.................................
我正在尝试执行一个查询,该查询LinkID
以下列方式更改每个批次:
- 以相同的方式获取每一行
LinkID
(例如,第一批是这里的前 6 行) - 按
Order
列排序 - 将
Data
和DataSpecifier
列视为一个比较单元(可以将它们视为一列,称为dataunit
): - 保持尽可能多的行
Order=1
,直到dataunit
出现在批次中多次出现的行 - 保留最后一行,但删除其余具有相同
LinkID
和更大Order
价值的行
所以对于LinkID
120
:
- 按
Order
列对批次进行排序(已在此处排序,但仍应这样做) - 从顶部开始(所以
Order=1
在这里),只要你没有看到在批次中出现超过 1 次的值 - 停在第一个重复项
Order=3
(dataunit
1 10
也是 onOrder
5
)。 - 删除所有具有
LinkID=120 AND Order>=4
LinkID
371
在对(以及表中的每个其他人)进行类似处理之后LinkID
,处理后的表将如下所示:
RowID LinkID Order Data DataSpecifier
1 120 1 1 1
2 120 2 1 3
3 120 3 1 10
7 371 1 6 2
8 371 2 3 5
9 371 3 8 1
10 371 4 10 1
11 371 5 7 2
.................................
.................................
我从来没有做过这么复杂的 SQL 查询。我知道查询必须是这样的:
DELETE FROM DataTable
WHERE RowID IN (SELECT RowID
FROM DataTable
WHERE -- ?
GROUP BY LinkID
HAVING COUNT(*) > 1 -- ?
ORDER BY [Order]);
但我似乎无法解决这个问题并正确查询。我最好在纯 SQL 中使用一个可执行(和可重用)查询来执行此操作。
我在这里问了一个非常相似的问题:如何从第一个重复项开始删除具有相同 ID 的其余行?
但是由于我意识到问题中的原始过滤逻辑实际上并不是我需要的,并且该问题已经得到正确回答,因此我不得不提出这个新问题。
解决方案
在这里,我之前的解决方案更新了。几个GROUP BY
应该就够了。代码很简单,可以通过阅读来理解。
设置:
IF OBJECT_ID('tempdb..#YourData') IS NOT NULL
DROP TABLE #YourData
CREATE TABLE #YourData (
RowID INT,
LinkID INT,
[Order] INT,
Data INT,
DataSpecifier INT)
INSERT INTO #YourData (
RowID,
LinkID,
[Order],
Data,
DataSpecifier)
VALUES
('1', ' 120', '1', '1', ' 1'),
('2', ' 120', '2', '1', ' 3'),
('3', ' 120', '3', '1', ' 10'),
('4', ' 120', '4', '1', ' 13'),
('5', ' 120', '5', '1', ' 10'),
('6', ' 120', '6', '1', ' 13'),
('7', ' 371', '1', '6', ' 2'),
('8', ' 371', '2', '3', ' 5'),
('9', ' 371', '3', '8', ' 1'),
('10', '371', '4', '10', '1'),
('11', '371', '5', '7', ' 2'),
('12', '371', '6', '3', ' 3'),
('13', '371', '7', '7', ' 2'),
('14', '371', '8', '17', '4')
解决方案:
;WITH DuplicatesByLinkID AS
(
SELECT
Y.LinkID,
Y.Data,
Y.DataSpecifier,
[Order] = MIN([Order])
FROM
#YourData AS Y
GROUP BY
Y.LinkID,
Y.Data,
Y.DataSpecifier
HAVING
COUNT(*) > 1
),
FirstDuplicateByLinkID AS
(
SELECT
D.LinkID,
MinOrder = MIN(D.[Order])
FROM
DuplicatesByLinkID AS D
GROUP BY
D.LinkID
)
DELETE Y FROM
#YourData AS Y
INNER JOIN FirstDuplicateByLinkID AS M ON
Y.LinkID = M.LinkID AND
Y.[Order] > M.MinOrder
SELECT * FROM #YourData
结果:
RowID LinkID Order Data DataSpecifier
1 120 1 1 1
2 120 2 1 3
3 120 3 1 10
7 371 1 6 2
8 371 2 3 5
9 371 3 8 1
10 371 4 10 1
11 371 5 7 2
推荐阅读
- python - Flask:@app.route 中的代码在第二次调用时失败(永远运行)
- javascript - 单线程 Node.js 如何并发处理请求?
- python - 如何通过 Python Azure SDK 了解 Azure blob 对象的大小
- sparql - 如何在sparql中表达等于关系?
- xml - XML 中的文本拆分问题
- rxjs - 我是否需要释放通过 fromEvent 运算符绑定的事件处理程序?
- c# - 如何将 Datetime.utcnow 转换为 DateTime,UTCtoLocal()?
- java - 如何在休眠中映射 Enum 0 以获取字符串标识符
- php - 在 solr 结果中隐藏路径(id)
- django - 如何将特定订单 pk 传递给 Django 中的引导模式?