SQL Server ORDER BY [aggregation] DESC/ASC

Question

我希望能够按升序或降序对[注释]进行排序。我尝试在"排序依据"子句中再次执行聚合，但这不起作用。我还尝试在子查询之外进行排序，这让我使用别名 [comment]，但这仅在第 0 行和第 10 行之间排序。

此查询是更大查询的较小版本。

SELECT *
FROM   (SELECT ISNULL((SELECT COUNT("order")
                       FROM   order_comment
                       WHERE  "order" = "order"."id"
                       GROUP  BY "order"), 0)                 AS [comment],
               Row_number() OVER (ORDER BY "order"."id" DESC) AS [rownum]
        FROM   [order]
        WHERE  ISNULL((SELECT COUNT("order")
                       FROM   order_comment
                       WHERE  "order" = "order"."id"
                       GROUP  BY "order"), 0) > 0) AS [filter]
WHERE  [rownum] BETWEEN 0 AND 10

在 MySql 中如此简单！

SELECT Ifnull((SELECT COUNT(`order`)
               FROM   order_comment
               WHERE  `order` = `order`.`id`
               GROUP  BY `order`), 0) AS `comment`
FROM   `order`
HAVING `comment` > 0
ORDER  BY `comment` DESC
LIMIT  0, 10  

Answer 1

关注最新的编辑怀疑你想要这样的东西

;WITH oc AS
(
SELECT [order], 
       COUNT([order]) AS order_count
FROM   order_comment 
GROUP  BY [order]
), occ AS
(
SELECT o.*,
       order_count AS comment,
       ROW_NUMBER() OVER (ORDER BY order_count DESC) AS  [rownum]
FROM [order] o
INNER JOIN oc ON oc.[order] = o.id
)
SELECT *
FROM occ 
WHERE  [rownum] BETWEEN 0 AND 10
ORDER BY [rownum]

INNER JOIN将排除任何没有子行的行order_comment

Answer 2

我假设你不知道你可以将OVER子句与聚合函数一起使用。

COUNT(order) OVER(PARTITION BY id) AS [comment]
....
ORDER BY [comment]

Answer 3

在 SQL Server 中，您可以按查询中的列号排序，如 ORDER BY 1更多信息在这里 http://blog.sqlauthority.com/2010/12/27/sql-server-order-by-columnname-vs-order-by-columnnumber/

SELECT *
FROM   (SELECT ISNULL((SELECT COUNT("order")
                      FROM   order_comment
                   WHERE  "order" = "order"."id"
                   GROUP  BY "order"), 0)                 AS [comment],
           Row_number() OVER (ORDER BY "order"."id" DESC) AS [rownum]
    FROM   [order]
    WHERE  ISNULL((SELECT COUNT("order")
                   FROM   order_comment
                   WHERE  "order" = "order"."id"
                   GROUP  BY "order"), 0) > 0) AS [filter]
WHERE  [rownum] BETWEEN 0 AND 10
 ORDER BY 1