Sql-Server
尋找一種更簡潔的方式從多個表中進行選擇
我目前正在使用此程式碼使用儲存過程從 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 在對問題的評論中提到的那樣)。