sql - 用两列对查询结果进行排序

我有这张 table :

Reply_ID | Fk_Post_ID
   10    |    5
   9     |    6
   8     |    5
   7     |    9
   6     |    5
   5     |    9
   4     |    7

我需要一个查询以以下顺序模式检索记录。它搜索具有最高reply_ID 的记录，然后检索具有相同Fk_Post_ID 的所有记录。像这样:

Reply_ID | Fk_Post_ID
   10    |    5
   8     |    5
   6     |    5
   9     |    6
   7     |    9
   5     |    9
   4     |    7

最佳答案

CREATE TABLE #YourTable (
    Reply_ID INT,
    fk_Post_ID INT
)

INSERT INTO #YourTable VALUES (10, 5)
INSERT INTO #YourTable VALUES (9, 6)
INSERT INTO #YourTable VALUES (8, 5)
INSERT INTO #YourTable VALUES (7, 9)
INSERT INTO #YourTable VALUES (6, 5)


SELECT
    t1.Reply_ID,
    t1.fk_Post_ID
FROM
    #YourTable t1 JOIN (
    SELECT
        MAX(Reply_ID) AS Max_Reply_ID,
        fk_Post_ID
    FROM #YourTable
    GROUP BY fk_Post_ID
) t2 ON t2.fk_Post_ID = t1.fk_Post_ID
ORDER BY
    t2.Max_Reply_ID DESC,
    t1.Reply_ID DESC

Sql Fiddle Here

关于sql - 用两列对查询结果进行排序，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13018713/