sql-server - sql双多列键

Question

它是一个 Sql 查询。

我想知道重复数据。

样本:

Table 1
Col1, col2, col3, col4
1, A, AA, AAA
2, A, BB, AAA
3, A, BB, AAA
4, B, AA, AAA
5, B, AA, BBB
6, B, AA, CCC
7, B, BB, AAA
8, B, CC, AAA

结果应该是:

2, A, BB, AAA
3, A, BB, AAA

或者

A, BB, AAA

所以我可以找到我的 double 在哪里。

谢谢你。

Answer 1

你也可以这样做:

测试数据

DECLARE @T TABLE(Col1 int, col2 VARCHAR(100), col3 VARCHAR(100), 
                 col4 VARCHAR(100))

INSERT INTO @T
VALUES
    (1, 'A', 'AA', 'AAA'),
    (2, 'A', 'BB', 'AAA'),
    (3, 'A', 'BB', 'AAA'),
    (4, 'B', 'AA', 'AAA'),
    (5, 'B', 'AA', 'BBB'),
    (6, 'B', 'AA', 'CCC'),
    (7, 'B', 'BB', 'AAA'),
    (8, 'B', 'CC', 'AAA')

查询1

;WITH CTE
AS
(   
    SELECT
        COUNT(Col1) OVER(PARTITION BY col2,col3,col4) AS Counts,
        T.*
    FROM
        @T AS T
)
SELECT
    *
FROM
    CTE
WHERE
    Counts>1

结果

2   2   A   BB  AAA
2   3   A   BB  AAA

查询2

;WITH CTE
AS
(   
    SELECT
        ROW_NUMBER() OVER(PARTITION BY col2,col3,col4 ORDER BY col1) AS RowNbr,
        T.*
    FROM
        @T AS T
)
SELECT
    *
FROM
    CTE
WHERE
    CTE.RowNbr>1

结果

2   3   A   BB  AAA