源码搜藏网

介绍

这篇文章/代码/提示引导SQL开发人员识别，高亮（计数），并删除重复表中的行。

设置舞台

让我们创建将包含我们的数据表。在这种情况下，我创建一个变量表。

DECLARE @tblLocation AS TABLE (
    ID INT PRIMARY KEY IDENTITY(1, 1),
    Name VARCHAR(50),
    Location VARCHAR(50)
);

让我们将一些数据。数据包括员工姓名，他前往的地方。在这种情况下，让我插入我参观了假想的位置！

隐藏   缩小    复制代码

INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Islamabad, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Lahore, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Jeddah, Saudi Arabia');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Lahore, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Washington DC, USA');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Albuqurque, USA');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Santa Fe, USA');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Albuqurque, USA');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Washington DC, USA');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'New York, USA');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Washington DC, USA');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Lahore, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Ankara, Turkey');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Istanbul, Turkey');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Ankara, Turkey');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Lahore, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Moscow, Russia');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Saint Petersburg, Russia');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Moscow, Russia');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Saint Petersburg, Russia');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Moscow, Russia');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Lahore, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Dubai, UAE');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Frankfurt, Germany');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Dusseldorf, Germany');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Essen, Germany');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Dusseldorf, Germany');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Wuppertal, Germany');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Dusseldorf, Germany');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Frankfurt, Germany');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Dubai, UAE');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Lahore, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Abu Dhabi, UAE');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Sharjah, UAE');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Abu Dhabi, UAE');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Sharjah, UAE');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Dubai, UAE');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Sharjah, UAE');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Abu Dhabi, UAE');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Lahore, Pakistan');

为了给更好地了解数据，让我们添加一些更多的数据。在这种情况下，说我朋友Shariq到过的地方。

INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Lahore, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Multan, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Lahore, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Abu Dhabi, UAE');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Lahore, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Karachi, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Lahore, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Rawalpindi, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Islamabad, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Lahore, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Dubai, UAE');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Macau, China');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Harbin, China');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Macau, China');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Dubai, UAE');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Lahore, Pakistan');

让我们来检查我们有什么？要做到这一点，我们执行一个简单的选择语句来获取数据。

SELECT    *
FROM    @tblLocation;

这条语句将返回56行。

让我们也检查了多少次雇员访问了位置。为了获取数据，我们将执行以下选择语句：

SELECT      Name, Location, COUNT(*)
FROM        @tblLocation
GROUP BY    Name, Location
ORDER BY    Name, COUNT(*) DESC;

结果如下：

我们想要的？

那么在我的情况，我想导出一个新表的位置列表。然而，在这种情况下，我们将试图删除重复的记录。

首先，我们需要确定我们要删除的记录。对于这一点，我们将使用ROW_NUMBER函数。

SELECT    ID, Name, Location, ROW_NUMBER() OVER (ORDER BY Name, Location) Occurance
FROM      @tblLocation
ORDER BY  Name, Location;;

这个选择语句将会给我们完整的数据与指定的最后一列递增数次数。

现在，我们运用PARTITION BY子句来划分次数上的基础列数据名称和位置。查询将是这样的：

SELECT    ID, Name, Location, ROW_NUMBER() OVER (PARTITION BY Name, _
          Location ORDER BY Name, Location) Occurance
FROM      @tblLocation
ORDER BY  Name, Location;

该语句的结果如下：

注意，ROW_NUMBER是每个员工前往不同的位置的基础上分配。我参观了阿布扎比3倍绿箱亮点。而看到的灰色框突出我的家乡七次。

现在正好有员工访问不同的位置，我们需要删除数据的所有这样的情况，次数大于1。

该删除声明将是这样的：

DELETE FROM @tblLocation
WHERE    ID IN (    SELECT    tbl.ID
                FROM    (    SELECT    ID,Name, Location, ROW_NUMBER() OVER (PARTITION BY Name, _
                             Location ORDER BY Name, Location) Occurance
                            FROM    @tblLocation
                            ) tbl
                WHERE    tbl.Occurance > 1
                );

执行上面的脚本后，只需使用选择数据选择语句中，我们会得到期望的结果。结果如下：

兴趣点

在这方面，我们学会了如何识别重复的记录，彰显他们算次数，最后删除重复记录。