尋找一種更簡潔的方式從多個表中進行選擇

我目前正在使用此程式碼使用儲存過程從 2 個表中進行選擇。

DECLARE @now as varchar(26)
set @now = CONVERT(VARCHAR(26), SYSDATETIME(), 9)

--Select 'SMD_' + replace(convert(varchar(7),DATEADD(MONTH, -1, GETDATE()),102), '.', '_') 

DECLARE @table1 AS varchar(12)
SET @table1  = 'SMD_' + replace(convert(varchar(7),DATEADD(MONTH, -0, GETDATE()),102), '.', '_')  

DECLARE @table2 AS varchar(12)
SET @table2  = 'SMD_' + replace(convert(varchar(7),DATEADD(MONTH, -1, GETDATE()),102), '.', '_')  

DECLARE @table3 AS varchar(12)
SET @table3  = 'SMD_' + replace(convert(varchar(7),DATEADD(MONTH, -2, GETDATE()),102), '.', '_')  

DECLARE @SQLQuery AS varchar(MAX)
SET @SQLQuery = '

insert into SMDTemp (QDATE, UPC2, AWEEK, WEEKRANGE, MOVEMENT, COST, RTL, LBS, GP) 
SELECT  
''' + @now + ''' as QDATE, UPC2,MAX(RTG_WKD.AWEEK),RTG_WKD.WEEKRANGE, SUM(SMVNUM) AS MOVEMENT, ROUND(AVG(SMVCOST), 2) AS COST, ROUND(AVG(SMVSUGS / SMVSUGNS), 2) AS RTL, CASE WHEN SUM(smvwgt) = 0 THEN SUM(smvnum) ELSE SUM(smvwgt) END AS LBS, (ROUND(AVG(SMVSUGS / SMVSUGNS),2) - ROUND(AVG(SMVCOST), 2)) / CASE WHEN round(AVG(smvsugs / smvsugns), 2) = 0 THEN NULL ELSE round(AVG(smvsugs / smvsugns), 2) END AS GP 
FROM ' + @table1 +'  INNER JOIN RTG_WKD ON SMVDATE = RTG_WKD.WKDDATE6 
where UPC2 = ''' + @search + '''
and SMVStore = ''' + @store + '''
GROUP BY RTG_WKD.WEEKRANGE, UPC2
union all 

SELECT  
''' + @now + ''' as QDATE, UPC2,MAX(RTG_WKD.AWEEK),RTG_WKD.WEEKRANGE, SUM(SMVNUM) AS MOVEMENT, ROUND(AVG(SMVCOST), 2) AS COST, ROUND(AVG(SMVSUGS / SMVSUGNS), 2) AS RTL, CASE WHEN SUM(smvwgt) = 0 THEN SUM(smvnum) ELSE SUM(smvwgt) END AS LBS, (ROUND(AVG(SMVSUGS / SMVSUGNS),2) - ROUND(AVG(SMVCOST), 2)) / CASE WHEN round(AVG(smvsugs / smvsugns), 2) = 0 THEN NULL ELSE round(AVG(smvsugs / smvsugns), 2) END AS GP 
FROM ' + @table2 +'  INNER JOIN RTG_WKD ON SMVDATE = RTG_WKD.WKDDATE6 
where UPC2 = ''' + @search + '''
and SMVStore = ''' + @store + '''
GROUP BY RTG_WKD.WEEKRANGE,  UPC2

Order by weekrange desc
'

EXECUTE(@SQLQuery)

SELECT * from SMDTemp
where QDATE = @now
and UPC2 = @search


delete from SMDTemp
where QDATE = @now

我現在需要添加第三張桌子，可能多達 12 張桌子。問題是當我添加第三張表時

UNION ALL 

SELECT  
''' + @now + ''' as QDATE, UPC2,MAX(RTG_WKD.AWEEK),RTG_WKD.WEEKRANGE, SUM(SMVNUM) AS MOVEMENT, ROUND(AVG(SMVCOST), 2) AS COST, ROUND(AVG(SMVSUGS / SMVSUGNS), 2) AS RTL, CASE WHEN SUM(smvwgt) = 0 THEN SUM(smvnum) ELSE SUM(smvwgt) END AS LBS, (ROUND(AVG(SMVSUGS / SMVSUGNS),2) - ROUND(AVG(SMVCOST), 2)) / CASE WHEN round(AVG(smvsugs / smvsugns), 2) = 0 THEN NULL ELSE round(AVG(smvsugs / smvsugns), 2) END AS GP 
FROM ' + @table3 +'  INNER JOIN RTG_WKD ON SMVDATE = RTG_WKD.WKDDATE6 
where UPC2 = ''' + @search + '''
and SMVStore = ''' + @store + '''
GROUP BY RTG_WKD.WEEKRANGE,  UPC2

我可以在 SQL Server Management Studio 中執行查詢，但是當我通過儲存過程執行它時，請求超時。

有沒有更清潔或更有效的方法來做到這一點？範常式式碼就足夠了，我可以自己插入數據。

首先，我會刪除它，Order by weekrange desc因為它是不必要的（因為您也沒有TOP()在任何SELECT查詢中使用它。

另外，為什麼UNION ALL首先使用？為什麼不讓這些是 3 - 12 個單獨的INSERT INTO table SELECT ....查詢？

而且，鑑於每個查詢中唯一變化的是表的名稱，並且該名稱遵循某種模式，因此可以對動態查詢進行模板化，然後循環遍歷您需要的月數。

以下應該做你想做的事，INSERT...SELECT每個單獨SMD_的表中的每一個都是一個單獨的查詢/事務：

DECLARE @Search NVARCHAR(10) = N'_search_', -- just to get the test code working
      @Store NVARCHAR(10) = N'_store_'; -- just to get the test code working

DECLARE @Now DATETIME2;
SET @Now = SYSDATETIME();

DECLARE @QueryTemplate AS NVARCHAR(MAX)
SET @QueryTemplate = N'
INSERT INTO SMDTemp (QDATE, UPC2, AWEEK, WEEKRANGE, MOVEMENT, COST, RTL, LBS, GP) 
 SELECT @Now_tmp AS [QDATE], UPC2, MAX(RTG_WKD.AWEEK), RTG_WKD.WEEKRANGE,
        SUM(SMVNUM) AS [MOVEMENT], ROUND(AVG(SMVCOST), 2) AS [COST],
        ROUND(AVG(SMVSUGS / SMVSUGNS), 2) AS [RTL],
        CASE WHEN SUM(smvwgt) = 0 THEN SUM(smvnum) ELSE SUM(smvwgt) END AS [LBS],
        (ROUND(AVG(SMVSUGS / SMVSUGNS),2) - ROUND(AVG(SMVCOST), 2)) / CASE
            WHEN round(AVG(smvsugs / smvsugns), 2) = 0 THEN NULL
             ELSE round(AVG(smvsugs / smvsugns), 2) END AS [GP]
 FROM SMD_{{TableDate}}
 INNER JOIN RTG_WKD
         ON SMVDATE = RTG_WKD.WKDDATE6
 WHERE UPC2 = @Search_tmp
 AND   SMVStore = @Store_tmp
 GROUP BY RTG_WKD.WEEKRANGE, UPC2;
';

DECLARE @SQLQuery NVARCHAR(MAX);
SET @SQLQuery = N'';

DECLARE @MonthsBack INT;
SET @MonthsBack = 0;

WHILE (@MonthsBack > -12)
BEGIN
 SET @SQLQuery = @SQLQuery + REPLACE(@QueryTemplate,
                                     N'{{TableDate}}',
                                     REPLACE(CONVERT(NVARCHAR(15),
                                                     DATEADD(MONTH,
                                                             @MonthsBack,
                                                             GETDATE()),
                                                     102),
                                             N'.',
                                             N'_')
                                             )
                           + NCHAR(0x0D) + NCHAR(0x0A);

 SET @MonthsBack = @MonthsBack - 1;
END;

PRINT @SQLQuery; -- debug

EXEC sp_executesql
 @SQLQuery, -- define the query
 N'@Now_tmp DATETIME2, @Search_tmp VARCHAR(100), @Store_tmp VARCHAR(100)', --param list
 @Now_tmp = @Now, -- pass in parameter: param_name = local_variable_name
 @Search_tmp = @Search, -- pass in parameter: param_name = local_variable_name
 @Store_tmp = @Store; -- pass in parameter: param_name = local_variable_name

該查詢最初是使用的EXEC(@SQLQuery)，但已被參數化以使用sp_executesql（正如@Jean 在對問題的評論中提到的那樣）。

引用自：https://dba.stackexchange.com/questions/120875