主页 / dba / 问题 / 111474
Accepted
Nelson
Asked: 2015-08-19 15:06:12 +0800 CST

使用 WHILE 语句优化查询

  • 772

我正在尝试将每年提取金额的视图转换为更多WHILE内容

查询或多或少是：

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

所以这返回：

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

例如，这对于生成图表非常好且易于使用。但正如我之前提到的，我开始研究这种查询，我相信它们可以变成更紧凑的WHILE循环。我知道旧时如果它没有坏就不要修复它所以任何输入都会被赞赏。

到目前为止，我正在围绕这些方面进行研究：

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

现在我一直在尝试获得与原始查询相同的输出，因为我WHILE的做法是成功的，但返回了 3 个不同查询的输出。我也无法设法CurrYear在每一轮中“获得”一个新的价值，有点像CurrYear + @intYear一种名字。

任何提示表示赞赏

sql-server-2008 optimization

2 个回答

  1. Best Answer

    迭代方法确实有其用武之地，但一般来说，在处理数据库时使用基于集合的方法要好得多。我多次听说数学上说“在绝大多数情况下，基于集合的解决方案比迭代解决方案更快”，但我目前似乎找不到很好的参考。

    如果您真的需要将其格式化为“像图表一样”，那么您将对数据进行透视。这是枢轴：

    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]

    这给你你想要的输出：

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

    另外，在分开时，我建议尝试提高清晰度，而不是压缩代码。虽然最好的代码行确实是您从未编写的代码行，但您编写的代码行必须是可维护的。话虽如此，PIVOT查询并不是最好的阅读方式，因为您正试图让 SQL Server 输出与其想要的相反的数据。让应用层格式化数据可能是值得的。那么您的查询将非常简单：

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

  2. 您可以使用动态 SQL 遍历这些年。

    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

相关问题