加入数据并优化性能

本文介绍了加入数据并优化性能的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我需要加入2个表中的数据，我需要优化性能。 我想通过工作步骤加入订单。

Hi, I need to join data from 2 table and I need to optimize the performance. I want to join orders with working steps.

Table 1 - Orders Barcode, OrderData, etc 184512, 20160412, ... 184752, 20160412, ... 184764, 20160413, ... Table 2 - Working Steps (idWorkingStep from 1 to 5) Barcode, Data, idWorkingStep 184512, 20160413, 1 184512, 20160413, 2 184512, 20160413, 4 184512, 20160413, 5 184764, 20160413, 2 184764, 20160414, 3 184752, 20160414, 4

需要的结果

Needed result

Barcode, count(idWorkingStep(1)) ws1, count(idWorkingStep(2)) ws2, count(idWorkingStep(3)) ws3, count(idWorkingStep(4)) ws4, count(idWorkingStep(5)) ws5 184512, 1, 1, 1, 1, 1 184764, 1, 1, 1, 0, 0 184752, 1, 1, 1, 1, 0

所有WorkingSteps都是必需的，因此如果扫描仪没有读取某个步骤而没有出现在表2中'出于同样的原因，我需要填写所有遗漏的信息。 正如您在结果表中看到的那样对于条形码184752我填充'1'从条形码的'表2'到最大（idWorkingStep）的所有步骤。 有时我使用它（脚本ex.2）：

All WorkingSteps are required, so if a step is not readed by the scanner and don't appears in the 'table 2' for same reason, I need to fill all the missing information. As you can see in the result table for then Barcode 184752 i fill with '1' all the steps up to the max(idWorkingStep) from 'Table 2' for that Barcode. Sometime I use this (script ex.2):

SELECT BarCode , CASE ws1 WHEN 0 THEN CASE ws2 WHEN 0 THEN CASE ws3 WHEN 0 THEN CASE ws4 WHEN 0 THEN '' ELSE 1 END ELSE 1 END ELSE 1 END ELSE CASE ws1 WHEN 0 THEN '' ELSE 1 END END ws1 , CASE ws2 WHEN 0 THEN CASE ws3 WHEN 0 THEN CASE ws4 WHEN 0 THEN '' ELSE 1 END ELSE 1 END ELSE 1 END ws2 , CASE ws3 WHEN 0 THEN CASE ws4 WHEN 0 THEN '' ELSE 1 END ELSE 1 END ws3 , CASE ws4 WHEN 0 THEN '' ELSE 1 END ws4

Tnx的回复 我尝试了什么： 我实际上使用视图对数据进行分组并使用： SELECT Max（v） FROM（VALUES（ws1），（ws2），（ws3），（ws4），（ws5））AS value（v））WHERE Barcode ='184512' 所有这些代码都是为了获得ws1 对于ws2，我使用： SELECT Max（v）FROM（VALUES（ws2），（ws3），（ws4），（ws5））AS值（v） ）WHERE Barcode ='184512' 可能不是最佳解决方案

Tnx for replies What I have tried: I actualy use views to group the data and I use: SELECT Max(v) FROM (VALUES (ws1), (ws2), (ws3), (ws4), (ws5)) AS value(v)) WHERE Barcode = '184512' All this code is to obtain ws1 For ws2 I use: SELECT Max(v) FROM (VALUES (ws2), (ws3), (ws4), (ws5)) AS value(v)) WHERE Barcode = '184512' Probably not the best solution

推荐答案

第一步（原谅pun）是为每个条形码生成所有可能步骤的列表。此查询将生成一个Table变量，其中包含示例中每个条形码的步骤1到5： First step (excuse the pun) is to generate a list of all the possible steps for each barcode. This query will generate a Table variable containing steps 1 to 5 for each of the barcodes in your sample: DECLARE @AllSteps TABLE (step int, barcode bigint) ;with CTE as ( select 1 as num, barcode from Working_Steps UNION ALL select CTE.num + 1 , Barcode from CTE where num < 5 ) INSERT INTO @AllSteps select DISTINCT * from CTE

你然后可以使用该表变量LEFT OUTER JOIN到表Working_Steps，就像这样

You can then use that table variable to LEFT OUTER JOIN to table Working_Steps like this

SELECT O.Barcode, A.step, WS.idWorkingStep FROM Orders O INNER JOIN @AllSteps A ON O.Barcode = A.BarCode LEFT OUTER JOIN Working_Steps WS ON WS.Barcode = O.Barcode AND WS.idWorkingStep=A.step group by O.Barcode, A.step, WS.idWorkingStep

获取以下结果（根据您在问题中的样本）

to get the following results (based on your sample in the question)

barcode step idWorkingStep 184512 1 1 184512 2 2 184512 3 NULL 184512 4 4 184512 5 5 184752 1 NULL 184752 2 NULL 184752 3 NULL 184752 4 4 184752 5 NULL 184764 1 NULL 184764 2 2 184764 3 3 184764 4 NULL 184764 5 NULL

然后你可以用一个简单的PIVOT来获取将数据转换为您想要的格式

You can then use a simple PIVOT to get the data into the format you want

SELECT * FROM ( SELECT O.Barcode, A.step, WS.idWorkingStep FROM Orders O INNER JOIN @AllSteps A ON O.Barcode = A.BarCode LEFT OUTER JOIN Working_Steps WS ON WS.Barcode = O.Barcode AND WS.idWorkingStep=A.step group by O.Barcode, A.step, WS.idWorkingStep ) AS psource PIVOT ( COUNT(idWorkingStep) FOR step in ([1],[2],[3],[4],[5]) ) AS pvt

这给了我以下结果

Which gave me the following results

barcode 1 2 3 4 5 184512 1 1 0 1 1 184752 0 0 0 1 0 184764 0 1 1 0 0

[EDI T] OP已经澄清了要求 - 可以假设在完成的最大步骤之前的序列中的任何间隙都已完成。对查询的这种调整将实现这一点（也包括@ RichardDeeming的建议）

OP has clarified the requirement - any gaps in the sequence earlier than the maximum step achieved can be assumed to have been done. This adjustment to the query will achieve that (incorporating @RichardDeeming's suggestion too)

DECLARE @AllSteps TABLE (step int, barcode bigint) INSERT INTO @AllSteps SELECT T.num, S.barcode FROM Working_Steps As S CROSS APPLY (VALUES (1), (2), (3), (4), (5)) As T (num) SELECT Barcode, [1],[2],[3],[4],[5] FROM ( SELECT O.Barcode, A.step, T.MaxPer, CASE WHEN A.step < T.MaxPer THEN A.step ELSE NULL END AS idWorkingStep FROM Orders O INNER JOIN @AllSteps A ON O.Barcode = A.BarCode INNER JOIN (SELECT MAX(idWorkingStep) as MaxPer, Barcode FROM Working_Steps GROUP BY Barcode) T ON O.Barcode=T.Barcode LEFT OUTER JOIN Working_Steps WS ON WS.Barcode = O.Barcode AND WS.idWorkingStep=A.step group by O.Barcode, A.step, t.MaxPer, WS.idWorkingStep ) AS psource PIVOT ( COUNT(idWorkingStep) FOR step in ([1],[2],[3],[4],[5]) ) AS pvt ORDER BY Barcode

产生结果

Which yields the results

barcode 1 2 3 4 5 184512 1 1 1 1 1 184752 1 1 1 1 0 184764 1 1 1 0 0

鉴于所有记录到最大记录的步骤都可以假定已经采取行动，那么我们真正感兴趣的是每个条形码记录的最大步数。因此根本不需要PIVOT - 可以通过以下方式生成相同的结果：

Given that all steps up to the maximum recorded can be assumed to have been actioned then all we are really interested in is the maximum step recorded per barcode. So there is no need for the PIVOT at all - the same results can be generated by the following:

DECLARE @MaxSteps TABLE(MaxPer int, barcode bigint) INSERT INTO @MaxSteps SELECT MAX(idWorkingStep) as MaxPer, Barcode FROM Working_Steps GROUP BY Barcode SELECT O.Barcode, [1] = CASE WHEN M.MaxPer >= 1 THEN 1 ELSE 0 END, [2] = CASE WHEN M.MaxPer >= 2 THEN 1 ELSE 0 END, [3] = CASE WHEN M.MaxPer >= 3 THEN 1 ELSE 0 END, [4] = CASE WHEN M.MaxPer >= 4 THEN 1 ELSE 0 END, [5] = CASE WHEN M.MaxPer >=5 THEN 1 ELSE 0 END FROM Orders O LEFT OUTER JOIN @MaxSteps M ON O.Barcode = M.Barcode