SQL Count / sum in order子句

SBB 发表于 Dev

SBB

我正在为正在使用的工具开发“排行榜”，我需要将一些数字汇总在一起并获取多行记录的数量。

您将在此存储过程中看到的是，我正在尝试按2列的总和对记录进行排序。

有关如何完成此操作的任何提示？

AS
BEGIN
SET NOCOUNT ON;
        BEGIN
            SELECT DISTINCT(whoAdded),
                    count(tag) as totalTags,
                    count(DISTINCT data) as totalSubmissions
            FROM   Tags_Accounts
            GROUP BY whoAdded
            ORDER BY SUM(totalTags + totalSubmissions) DESC
            FOR    XML PATH ('leaderboard'), TYPE, ELEMENTS, ROOT ('root');
        END 
END

杰拉德·罗斯（Jerad Rose）

您可以通过将其放在派生表中来完成此操作：

SELECT *
FROM (
    SELECT DISTINCT(whoAdded) AS whoAdded,
        count(tag) as totalTags,
        count(DISTINCT data) as totalSubmissions
    FROM   Tags_Accounts
    GROUP BY whoAdded
    ) a
ORDER BY totalTags + totalSubmissions DESC
FOR    XML PATH ('leaderboard'), TYPE, ELEMENTS, ROOT ('root')

另外，您也可以按集合排序，但是我认为以上内容更简洁/更少冗余：

SELECT DISTINCT(whoAdded) as whoAdded,
    count(tag) as totalTags,
    count(DISTINCT data) as totalSubmissions
FROM   Tags_Accounts
GROUP BY whoAdded
ORDER BY SUM(count(tag) + count(DISTINCT data)) DESC
FOR    XML PATH ('leaderboard'), TYPE, ELEMENTS, ROOT ('root')

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。