SQL Server 2008 R2: 删除单个记录。

Question

我有一个包含两列的表格，如下所示：

CREATE TABLE test_lin
(
    Cola INT,
    Colb INT
);

INSERT INTO test_lin VALUES(1,2);
INSERT INTO test_lin VALUES(1,3);
INSERT INTO test_lin VALUES(1,4);
INSERT INTO test_lin VALUES(1,5);
INSERT INTO test_lin VALUES(1,3);
INSERT INTO test_lin VALUES(2,4);
INSERT INTO test_lin VALUES(2,6);
INSERT INTO test_lin VALUES(2,7);
INSERT INTO test_lin VALUES(2,4);
INSERT INTO test_lin VALUES(2,6);

注意：现在我想只显示那些重复出现超过一次的记录。就像在我的情况下，(1,3)，(2,4)和(2,6)这些记录在表中重复出现。

我想从结果集中删除单个出现的记录。如下图所示，单个出现的记录如下：

- MAK

Answer 1

尝试这个：

  WITH cte AS 
 (SELECT cola, colb
  FROM test_lin
  GROUP BY cola, colb
  HAVING count(*) > 1)
  SELECT l.*
  from test_lin l INNER JOIN CTE ON 
       l.cola = cte.cola and l.colb=cte.colb

- BICube

---

我想要显示重复的记录。这可能吗？ - MAK

不知道你需要整个记录。Dark Knight的答案显然已经被接受，但这个版本也适合你。 - BICube

需要从外部SELECT语句中删除“DISTINCT”，然后结果将是预期的结果。非常感谢您的帮助。我一定会为您的努力点赞。 - MAK

Answer 2

WITH CTE as 
(
SELECT Cola,Colb,ROW_NUMBER() OVER(PARTITION BY Cola,Colb ORDER BY Cola) AS RN
FROM test_lin
)

SELECT T.* 
FROM test_lin T INNER JOIN  CTE C ON C.COLa = t.Cola and C.COLb = t.Colb and c.RN=2

- Sateesh Pagolu

Answer 3

请查看这个示例，我从@Ala的示例中借鉴并添加了一些内容：

1）第一个选项

select * from 
(
 SELECT *
  FROM test_lin
  GROUP BY cola, colb
  HAVING count(*) > 1
  ) cte
join test_lin l  ON 
 l.cola = cte.cola and l.colb=cte.colb

2) @Ala的回答中有一个更正。

;WITH cte AS 
 (SELECT cola, colb
  FROM test_lin
  GROUP BY cola, colb
  HAVING count(*) > 1)
  SELECT  l.*   --remove distinct only
  from test_lin l INNER JOIN CTE ON 
       l.cola = cte.cola and l.colb=cte.colb

- Ajay2707

---

请重新检查您脚本的结果。 - MAK