Início / dba / Perguntas / 2565
Accepted
kacalapy
Asked: 2011-05-07 06:46:05 +0800 CST

A maneira mais eficiente de retornar vários agregados em um único procedimento armazenado?

  • 772

Qual é a melhor ou mais eficiente maneira de obter vários valores de resultados agregados?

Basicamente, tenho um aplicativo de e-mail e queria obter todos os números de mensagens para cada tipo de pasta (caixa de entrada, enviada, arquivada, sinalizada...) conforme definido abaixo.

Aqui está uma amostra de coisas que estou tentando fazer em uma chamada de procedimento armazenado. (onde [uid] é o userID)

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 respostas

  1. Best Answer

    COUNT..CASE é a maneira usual. Para "Enviado", é um pouco mais complicado porque é um filtro uid diferente que requer 2 consultas na tabela de mensagens

    Eu usei essa construção para permitir linha zero para/de. Evita um OR e adiciona um filtro uid a cada CASE.

    Cada tabela derivada (pode escrever como um CTE) retornará apenas uma linha e usei um MAX em uid para evitar GROUP BY também, caso alguém se pergunte

    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

    Editar:

    Uma agregação sem GROUP BY sempre retornará um resultado (consulte SO aqui ). Eu esqueci disso.

    Eu atualizei meu 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

relate perguntas