Início / dba / Perguntas / 111474
Accepted
Nelson
Asked: 2015-08-19 15:06:12 +0800 CST

Otimizando a consulta usando uma instrução WHILE

  • 772

Estou tentando converter uma exibição que extrai uma quantia anual em algo mais WHILEish

A consulta é mais ou menos:

SELECT *
FROM
(SELECT SUM(EmployeePaid)*2 AS Total2013 FROM TblRecords WHERE FYear = 2013) t1,
(SELECT SUM(EmployeePaid)*2 AS Total2014 FROM TblRecords WHERE FYear = 2014) t2,
(SELECT SUM(EmployeePaid)*2 AS Total2015 FROM TblRecords WHERE FYear = 2015) t3

Então isso retorna:

=====================================
‖ Total2013 ‖ Total2014 ‖ Total2015 ‖
=====================================
‖ 42983.31  ‖ 76345.10  ‖  87233.5  ‖
=====================================

O que é bom e fácil de usar para gerar um gráfico, por exemplo. Mas como mencionei anteriormente comecei a pesquisar esse tipo de consulta e acredito que possam se transformar em algo mais compacto como em um WHILEciclo. Estou ciente dos velhos tempos. Se não estiver quebrado, não conserte, então qualquer entrada no assunto será apreciada.

Até agora estou entrando em algo em torno destas linhas:

DECLARE @intYear INT
SET @intYear = 2013
WHILE (@intYear < Year(GETDATE()))
BEGIN
    SELECT SUM(EmployeePaid)*2 AS CurrYear FROM TblRecords WHERE FYear = @intYear
    SET @intYear = @intYear + 1
END

No momento, estou tentando obter a mesma saída da consulta original, já que WHILEestou fazendo o truque, mas retornando a saída para 3 consultas diferentes. Também não consigo CurrYear"ganhar" um novo valor a cada rodada, como uma CurrYear + @intYearespécie de nome.

Qualquer dica é apreciada

sql-server-2008 optimization

2 respostas

  1. Best Answer

    Abordagens iterativas têm seu lugar, mas, de modo geral, é muito melhor usar abordagens baseadas em conjunto ao trabalhar com bancos de dados. Ouvi dizer que a matemática diz que " soluções baseadas em conjunto serão mais rápidas do que soluções iterativas na grande maioria dos casos " várias vezes, mas não consigo encontrar uma boa referência no momento.

    Se você realmente precisa formatá-lo "como um gráfico", dinamize os dados . Aqui está o pivô:

    SELECT 
    *
FROM 
(
    SELECT 
        [EmployeePaid] * 2 AS [EmployeePaid] -- Why 2x?
        , N'Total ' + CAST([FYear] AS NVARCHAR(4)) AS [Year] -- This output must match the 'IN' clause below
    FROM [TblRecords]
) as s
PIVOT
(
    SUM([EmployeePaid) -- Aggregate
    FOR [Year] IN ([Total 2013], [Total 2014], [Total 2015]) -- Match to pivoted columns
)as [pivot]

    Isso lhe dá a saída que você queria:

    ========================================
‖ Total 2013 ‖ Total 2014 ‖ Total 2015 ‖
========================================
‖ 42983.31   ‖ 76345.10   ‖  87233.5   ‖
========================================

    Também na parte, eu recomendaria tentar aumentar a clareza em vez de compactar seu código. Embora seja verdade que a melhor linha de código é aquela que você nunca escreve, as linhas de código que você escreve devem ser sustentáveis. Dito isto, PIVOTas consultas não são as mais agradáveis ​​de ler porque você está tentando fazer com que o SQL Server gere dados contrários ao que deseja. Pode valer a pena fazer com que a camada de aplicativo formate os dados. Então sua consulta seria tão simples quanto:

    SELECT
    [FYear]
    , SUM([EmployeePaid]) * 2 AS [YearTotal] -- Why 2x?
FROM [TblRecords]
WHERE
    [FYear] IN (2013, 2014, 2015)
GROUP BY
    [FYear]
    • 4

  2. Você pode usar SQL dinâmico para percorrer os anos.

    DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'SELECT ';

DECLARE @intYear INT
SET @intYear = 2013
WHILE (@intYear < Year(GETDATE()))
BEGIN
    SET @SQL = @SQL + '(SELECT SUM(EmployeePaid)*2 AS CurrYear FROM TblRecords WHERE FYear = ' + CAST(@intYear as CHAR(4)) + ') as Total' + CAST(@intYear as CHAR(4)) + ',';
    SET @intYear = @intYear + 1
END

SET @SQL = SUBSTRING(@SQL,1,LEN(@SQL) -1)

EXEC(@SQL)
    • 1

relate perguntas