主页 / dba / 问题 / 2565
Accepted
kacalapy
Asked: 2011-05-07 06:46:05 +0800 CST

在单个存储过程中返回多个聚合的最有效方法？

  • 772

获取多个聚合结果值的最佳或最有效方法是什么？

基本上我有一个电子邮件应用程序，想要获取每种类型的文件夹（收件箱、已发送、已存档、已标记...）的所有消息数量，如下定义。

这是我在一个存储过程调用中尝试做的事情的示例。（其中 [uid] 是用户 ID）

SELECT * FROM Message
Inbox    WHERE [to]    = [uid] 
Unread    WHERE [to]    = [uid] and isread = 0
Flagged    WHERE [to]    = [uid] and isFlagged = 1
Drafts    WHERE [to]    = [uid] and isDraft = 1
Sent Messages    WHERE [from] = [uid]
Archived Messages    WHERE [to]     = [uid] and isArchived = 1
sql-server-2005 stored-procedures

2 个回答

  1. Best Answer

    COUNT..CASE 是通常的方式。对于“已发送”虽然有点棘手，因为它是一个不同的 uid 过滤器，需要在消息表上进行 2 次查询

    我已经使用此构造来允许零行到/从。它避免了 OR 并为每个 CASE 添加了一个 uid 过滤器。

    每个派生表（可以写成 CTE）将只返回一行，我在 uid 上使用了 MAX 来避免 GROUP BY 以防万一有人想知道

    SELECT
    ISNULL(M.Inbox, 0) AS Inbox,
    ISNULL(M.Unread, 0) AS Unread,
    ISNULL(M.Flagged, 0) AS Flagged,
    ISNULL(M.Drafts, 0) AS Drafts,
    ISNULL(S.Sent, 0) AS Sent,
    ISNULL(M.Archived, 0) AS Archived
FROM
    (
    SELECT
        COUNT(*) AS InBox,
        COUNT(CASE WHEN isread = 0 THEN 1 ELSE NULL END) Unread,
        COUNT(CASE WHEN isFlagged = 1 THEN 1 ELSE NULL END) Flagged,
        COUNT(CASE WHEN isDraft = 1 THEN 1 ELSE NULL END) Drafts,
        S.Sent,
        COUNT(CASE WHEN isArchived = 0 THEN 1 ELSE NULL END) Archived
    FROM
        Message
    WHERE
        [to] = [uid] 
    ) M
    FULL OUTER JOIN
    (
    SELECT COUNT(*) AS Sent
    FROM Message
    WHERE [from] = [uid]
    ) S ON 1=1

    编辑：

    没有 GROUP BY 的聚合将始终返回结果（请参见此处的 SO ）。我忘记了。

    我更新了我的 SQL

    • 7 
  2. DECLARE @Inbox  bigint
DECLARE @Unread bigint
DECLARE @Flagged bigint
/*
MORE DECLARE STATEMENTS GO HERE
*/

SET @Inbox   = SELECT Count(*) FROM MESSAGE WHERE [to] = [uid] 
SET @Unread  = SELECT Count(*) FROM MESSAGE WHERE [to] = [uid] and isread = 0
SET @Flagged = SELECT Count(*) FROM MESSAGE WHERE [to] = [uid] and isFlagged = 1

/*
MORE SELECT STATEMENTS GO HERE
*/

SELECT
@Inbox   as Inbox,
@Unread  as Unread,
@Flagged as Flagged

/*
MORE @Variables GO HERE
*/
    • 2

相关问题