陷入 t-SQL 中单个表的选择性 DELETE 语句中 [英] Got mired in a selective DELETE statement on a single table in t-SQL

问题描述

比如说，我有一个这样的表(SQL Server 2008):

Say, I have a table like this (SQL Server 2008):

CREATE TABLE tbl (ID INT, dtIn DATETIME2, dtOut DATETIME2, Type INT)

INSERT tbl VALUES
(1, '05:00', '6:00', 1),  -- will be removed
(2, '05:00', '7:00', 1),  -- will be removed
(3, '05:01', '8:00', 1),
(4, '05:00', '8:00', 1),
(5, '05:00', '6:00', 2),  -- will be removed
(6, '05:00', '7:00', 2),
(7, '05:00', '7:00', 3),
(8, '04:00', '7:00', 3)

我需要删除所有相同类型"的记录(如果找到 2 个或更多)，它们的类型"具有相同的dtIn"，除了dtOut"最大的记录.换句话说，上面的表格应该是这样的:

I need to remove all records of the same 'type' (if 2 or more are found) with the same 'dtIn' for their 'type', except the one with the largest 'dtOut'. In other words, the table above should result in this:

(3, '05:01', '8:00', 1),   -- no matching 'dtIn' for 'type' = 1
(4, '05:00', '8:00', 1),   -- largest 'dtOut' for 'type' = 1
(6, '05:00', '7:00', 2),   -- largest 'dtOut' for 'type' = 2
(7, '05:00', '7:00', 3),   -- no matching 'dtIn' for 'type' = 3
(8, '04:00', '7:00', 3)    -- no matching 'dtIn' for 'type' = 4

你如何从同一个表中选择几行相同类型的行.你不能选择 * from tbl where type=type....无论如何，我很感激这方面的一些帮助...

How do you even select several rows with the equal type from the same table. You can't do select * from tbl where type=type.... Anyway, I'd appreciate some help with this...

推荐答案

这是选择要删除的行的一种方法:

Here's one way to select the rows you want to delete:

SELECT *
FROM tbl T1
WHERE EXISTS
(
    SELECT *
    FROM tbl T2
    WHERE T1.Type = T2.Type
    AND T1.dtIn = T2.dtIn
    AND (
            T1.dtOut < T2.dtOut
            OR (T1.dtOut = T2.dtOut AND T1.id < T2.id)
        )
)

这个查询也可以很容易地更改为实际删除行.只需将 SELECT * 更改为 DELETE T1.但请在实际运行 delete 语句之前测试它是否满足您的要求.

This query can also be easily changed to actually delete the rows. Just change SELECT * to DELETE T1. But please do test that it does what you want before actually running the delete statement.

在线查看:sqlfiddle

更新

这是使用 ROW_NUMBER 的方法:

Here's an approach using ROW_NUMBER:

;WITH T1 AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY Type, dtIn
                                  ORDER BY dtOut DESC, ID DESC) AS rn
    FROM tbl
)
SELECT * FROM tbl
WHERE id IN
(
    SELECT id
    FROM T1
    WHERE rn > 1
)

在线查看:sqlfiddle

这篇关于陷入 t-SQL 中单个表的选择性 DELETE 语句中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！