sql - 选择具有最高 ID 和指定列值的行

Question

我有一个 sql 查询问题。我有下表:

╔════╦═══════════════╦═════════════╦══════════╗
║ ID ║     NAME      ║    CITY     ║ PCNUMBER ║
╠════╬═══════════════╬═════════════╬══════════╣
║  1 ║ John Doe      ║ New York    ║       42 ║
║  2 ║ John Doe      ║ New York    ║       42 ║
║  3 ║ John Doe      ║ New York    ║       42 ║
║  4 ║ John Doe      ║ Los Angeles ║       42 ║
║  5 ║ Ellen Ripley  ║ Nostromo    ║     2092 ║
║  6 ║ James T. Kirk ║ Riverside   ║     2233 ║
║  7 ║ Henry Jones   ║ Chicago     ║     1899 ║
╚════╩═══════════════╩═════════════╩══════════╝

现在我想获取具有指定 PCNUMBER(可以大于 1)的行，但它只能为每个唯一的 PCNUMBER 返回 1 个结果

所以让我们假设我想要来自 PCNUMBER 42 和 2092 的数据。结果是:

╔════╦══════════════╦═════════════╦══════════╗
║ ID ║     NAME     ║    CITY     ║ PCNUMBER ║
╠════╬══════════════╬═════════════╬══════════╣
║  4 ║ John Doe     ║ Los Angeles ║       42 ║
║  5 ║ Ellen Ripley ║ Nostromo    ║     2092 ║
╚════╩══════════════╩═════════════╩══════════╝

我已经尝试了很多东西，但我似乎无法做到这一点:(希望你们能帮助我。

如果有人已经有类似的问题，但我找不到，我很抱歉。

Answer 1

SQL Server 支持 Common Table Expression和 Windowing Functions .

WITH records
AS
(
    SELECT  ID, Name, City, PCNUMBER,
            ROW_NUMBER() OVER(PARTITION BY PCNUMBER ORDER BY ID DESC) rn
    FROM    TableName
)
SELECT  ID, Name, City, PCNUMBER
FROM    records
WHERE   rn = 1 AND
        PCNUMBER IN (42, 2092)

SQLFiddle Demo

TSQL Ranking Functions

输出

╔════╦══════════════╦═════════════╦══════════╗
║ ID ║     NAME     ║    CITY     ║ PCNUMBER ║
╠════╬══════════════╬═════════════╬══════════╣
║  4 ║ John Doe     ║ Los Angeles ║       42 ║
║  5 ║ Ellen Ripley ║ Nostromo    ║     2092 ║
╚════╩══════════════╩═════════════╩══════════╝