时间戳

首页 > 解决方案 > 如何在 SQL Server 2005 中的变量中选择多个行值？

sql - 如何在 SQL Server 2005 中的变量中选择多个行值？

问题描述

我写了一个定义变量的脚本。

其中一个变量是这样定义的。

SET @Yesterday = (SELECT SUM (ri.Sales_Quantity) 
                  FROM ReportTransactions AS RT     
                  LEFT JOIN ReportItems AS ri ON rt.Report_Transaction_ID = ri.Report_Transaction_ID    
                  LEFT JOIN MMGroups AS mmg ON ri.MMGroup2_ID = MMG.ID  
                  LEFT JOIN Locations AS L ON L.Location_ID = RT.Store_ID 
                  WHERE rt.Transaction_Date > DATEADD(DAY, -1, GETDATE()) 
                    AND (NULL IS NULL OR rt.Store_ID = NULL)   
                    AND (rt.Training_Mode IS NULL OR rt.Training_Mode = 0)   
                    AND ri.Item_Voided = 0   
                    AND ri.Void_Type = 0
                    AND rt.Transaction_Type IN (0, 1, 2, 3, 4, 5, 6)   
                    AND ri.Item_Type IN (1, 2, 3, 4, 6, 7, 54, 55, 81, 84, 11, 12, 13, 22, 25, 27, 82) 
                  GROUP BY  
                      RT.Store_ID);

但是，这会返回多个值，因此无法定义变量并引发错误。

我的报告需要所有这些行，所以我如何定义它们并检索它们，最佳实践是什么？

标签： sqlsql-server-2005

解决方案

也许你可以尝试一个表变量。

DECLARE @Yesterday  Table
(
Sum_Qty  int
)
insert into @Yesterday
SELECT SUM (ri.Sales_Quantity) 
                  FROM ReportTransactions AS RT     
                  LEFT JOIN ReportItems AS ri ON rt.Report_Transaction_ID = ri.Report_Transaction_ID    
                  LEFT JOIN MMGroups AS mmg ON ri.MMGroup2_ID = MMG.ID  
                  LEFT JOIN Locations AS L ON L.Location_ID = RT.Store_ID 
                  WHERE rt.Transaction_Date > DATEADD(DAY, -1, GETDATE()) 
                    AND (NULL IS NULL OR rt.Store_ID = NULL)   
                    AND (rt.Training_Mode IS NULL OR rt.Training_Mode = 0)   
                    AND ri.Item_Voided = 0   
                    AND ri.Void_Type = 0
                    AND rt.Transaction_Type IN (0, 1, 2, 3, 4, 5, 6)   
                    AND ri.Item_Type IN (1, 2, 3, 4, 6, 7, 54, 55, 81, 84, 11, 12, 13, 22, 25, 27, 82) 
                  GROUP BY  
                      RT.Store_ID

推荐阅读