Início / dba / Perguntas / 19769
Accepted
NReilingh
Asked: 2012-06-23 14:11:15 +0800 CST

Como evitar a duplicação de informações em uma série de instruções SELECT aninhadas?

  • 772

Estou trabalhando em uma consulta SQL que reúne o histórico do cliente com base em compras anteriores (o setor é a venda de ingressos). O relatório será GRANDE, algo como 80-90 colunas, com uma coluna por evento ou categoria de evento por registro no CSV de saída.

A estrutura do banco de dados exige que eu use instruções de seleção aninhadas para obter os dados necessários em colunas para cada registro--cada coluna contendo uma quantidade de ingressos para esse tipo de evento. Eu sei como fazer isso em teoria, mas cada instrução de seleção incorporada é tão grande que o relatório completo ultrapassaria o limite de 8.000 caracteres na minha interface de banco de dados. Uma dessas declarações se parece com isso:

(select count (*)
FROM
guest ig, event2 e, eventseat es, "order" o
WHERE ig.guestid = g.guestid
and ig.guestid = o.guestid
and o.orderid = es.orderid
and e.eventid = es.eventid
and e.incometype = 'T'
and e.eventtype in ('SS', 'BMF')
and es.status in ('2','4')
and es.price <> '0.00'
and es.price <> '5.00'
and e.year = '2010'       <-- THESE LINES ARE THE ONLY ONES THAT 
and e.run in ('SS-DANCE') <-- CHANGE PER SELECT STATEMENT 
) as 'SS-Dance',

São cerca de 400 caracteres, o que deixa espaço suficiente para menos de 20 iterações em uma única consulta sem ultrapassar o limite de caracteres.

Existe uma maneira de reduzir isso ou generalizá-lo para que eu não precise desperdiçar todos esses caracteres com informações duplicadas em cada SELECT?

Montei um exemplo no link a seguir que contém uma versão simplificada da minha estrutura de banco de dados e alguns dados de amostra. O objetivo é obter os mesmos dados de resultado sem usar tantos caracteres por SELECT aninhado.

http://sqlfiddle.com/#!3/fc316/2

sql-server sql-server-2005

1 respostas

  1. Best Answer
    ;WITH x AS 
(
  SELECT g.guestid, e.[year], e.run, c = COUNT(*)
  FROM dbo.guest AS g
  INNER JOIN dbo.[order] AS o
  ON g.guestid = o.guestid
  INNER JOIN dbo.eventseat AS es
  ON o.orderid = es.orderid
  INNER JOIN dbo.event2 AS e
  ON e.eventid = es.eventid
  WHERE e.incometype = 'T'
  AND e.eventtype in ('SS', 'BMF')
  AND es.status in ('2','4')
  AND es.price NOT IN ( '0.00', '5.00')
  AND e.[year] = '2010'
  AND e.run IN ('SS-DANCE', 'SS-FILM', 'SS-OPERA')
  GROUP BY g.guestid, e.[Year], e.run
),
y AS 
(
  SELECT * FROM x PIVOT (MAX(c) FOR run IN 
  ([SS-DANCE],[SS-FILM],[SS-OPERA])) AS z
)
SELECT y.guestid, y.[year], g.[first], g.[last], 
  [SS-DANCE] = COALESCE(y.[SS-DANCE], 0), 
  [SS-FILM]  = COALESCE(y.[SS-FILM], 0), 
  [SS-OPERA] = COALESCE(y.[SS-OPERA], 0)
FROM y 
INNER JOIN dbo.guest AS g
ON y.guestid = g.guestid;

    Não tenho certeza se minha cláusula where vai funcionar para você como está escrito. Atualmente, isso pressupõe que você se preocupa apenas com esses três tipos (dança, filme e ópera) e se preocupa apenas com o ano de 2010. Se você precisar cruzar os anos, poderá remover essa WHEREcláusula. Se você se preocupa apenas com a dança em 2010 e com o cinema em qualquer ano, precisará escrever um conjunto mais complexo de cláusulas where.

    Alguns comentários:

    • tente evitar palavras reservadas como order, yeare . Estes apenas complicam as coisas porque precisam ser colocados entre colchetes (que são preferíveis principalmente para facilitar a leitura e não ambiguidade).firstlast"double quotes"
    • sempre use dbo.(ou qualquer schemaprefixo apropriado).
    • como sugeri acima, atenha-se à INNER JOINsintaxe explícita. FROM x, y, zé para os pássaros.
    • 6

relate perguntas