用信息获取副本

Question

编辑作者：这个问题被错误地描述了。Here's the rephrased one。

我继承了一个数据库，在构建一个工作的SQL查询时遇到了困难。

假设这是数据：

[Table]

| Id    | DisplayId     | Sequence  | Type      | Description   |
|----   |-----------    |---------- |-----------| -----------   |
| 1     | 12345         | 0         | 16        | Random        |
| 2     | 12345         | 0         | 2         | Random 2      |
| 3     | AB123         | 0         | 1         | Random 3      |
| 4     | 12345         | 1         | 16        | Random 4      |
| 5     | 12345         | 1         | 2         | Random 5      |
| 6     | XX45          | 0         | 5         | Random 6      |
| 7     | 12345         | 2         | 16        | Random 7      |
| 8     | 12345         | 2         | 2         | Random 8      |
| 9     | XX45          | 1         | 5         | Random 9      |
| 10    | XX45          | 2         | 5         | Random 10     |
| 11    | XX45          | 2         | 12        | Random 11     |
| 12    | 12345         | 3         | 16        | Random 12     |


[Type]

| Id    | State     |
|----   |-----------|
| 1     | 'ABC'     |
| 2     | '456'     |
| 5     | 'XYZ'     |
| 12    | 'XYZ'     |
| 16    | '456'     |

Type列是引用Type表的外键。现在，当只比较DisplayId和Type.State时，我需要选择重复的行，然后只显示每个DisplayId / Type.State集的最高Sequence。另外，应该使用Id列来连接其他数据(例如，OtherTable.Title)。

因此，对于上述数据，这将是预期的结果：

| Id    | DisplayId     | Sequence  | Type      | Description   | OtherTable.Title  |
|----   |-----------    |---------- |-----------|-------------  |------------------ |
| 8     | 12345         | 2         | 2         | Random 8      | Title 8           |
| 10    | XX45          | 2         | 5         | Random 10     | Title 10          |
| 11    | XX45          | 2         | 12        | Random 11     | Title 11          |
| 12    | 12345         | 3         | 16        | Random 12     | Title 12          |

我设法获得了比较和最高序列选择的工作，以获得一个不同的DisplayId/Type有重复的列表，但一旦我再次插入Id列，以显示额外的数据，所有都是混乱.

SELECT
    P.DisplayId, P.Type
FROM
    Table P
INNER JOIN
    (SELECT DisplayId, MAX(Sequence) AS Seq FROM Table GROUP BY DisplayId) HighSeq ON P.DisplayId = HighSeq.DisplayId AND P.Sequence = HighSeq.Seq
GROUP BY
    P.DisplayId, P.Type
HAVING
    COUNT(*) > 1

我很想了解你的见解..。

Answer 1

使用行号

;WITH CTE
AS
(
    SELECT
        RN = ROW_NUMBER() OVER(PARTITION BY DisplayId,[Sequence] ORDER BY DisplayId,[Sequence]),
        Id ,
        DisplayId,
        [Sequence],
        [Type],
        [Description]
        FROM YourTable
)
SELECT
    *
    FROM CTE
        INNER JOIN YourTable2 YT2
            ON CTE.ID = YT2.ID
        WHERE CTE.RN > 1

您可以使用CTE连接其他表，就像对普通表一样。

Answer 2

你可以用这个。

SELECT T.*, OT.Title 
    FROM (
    SELECT M.*, Type.State, 
        RN = ROW_NUMBER() OVER (PARTITION BY DisplayId, M.[Type], Type.State ORDER BY Sequence DESC ),
        CNT = COUNT(M.Id) OVER (PARTITION BY DisplayId, Type.State )
    FROM MyTable M
    INNER JOIN Type Type ON M.[Type] = Type.Id
) T 
INNER JOIN OtherTable OT ON T.Id = OT.ID
WHERE 
    T.RN = 1
    AND T.CNT > 1
ORDER BY Id

Answer 3

你可以试试这个：

select ID,a.displayid,sequence,type, table2.Title from
(select ID,displayid,sequence,type, Row_number() over (partition by displayid,Type order by sequence desc) rn
from table1) a inner join table2 on a.id = table2.id and a.rn=1