SQL Server - 字符串型和整型的更好连接方式

Question

我希望你能够帮我找到有效的方法来加速我的查询。让我描述一下现状。

每天晚上，我们会将来自不同供应商的报告转储到一张表中。这个表有很多列，但下面这些列是我在查询中使用的主要列。

tblVendorShippingInfo（请注意，下列所有列都是VARCHAR类型，因为有时候报告中甚至包含varchar字符，即使是在QtyShipped列中也是如此）

PONum     vSKU    vSKUDesc     ShipQuantity
P111111   A       Test1       5
P111111   A       Test1       2
P111111   B       Test3       5
P100002   C       Test4       6
P222222   D       Test5       7
P222222   D       Test5       6
P222222   E       Test6       7
PABC123   F       Test7       8
XYZ123    G       Test8       8
GHEHEH    H       Test9       8

tblPODetail -- 我们的采购订单表。请注意，POID和DistyShippedQty都是整数。POID只有6位数字。

POID      SKU     SKUDesc     DistyShippedQty
111111    A       Test1       
111111    B       Test3       
100002    C       Test4       
222222    D       Test5       
222222    E       Test6           

如果我使用以下这样的查询来获取和更新DistyShippedQty，虽然会起作用，但速度会很慢，因为要进行转换匹配VARCHAR等操作。

select POID, SKU
, (
    SELECT SUM(ShipQuantity) AS ShipQuantity 
    FROM tblVendorShippingInfo
    WHERE substring(PONum, 2, 6) = Convert(varchar(10), pod.POID)
    AND vSKU = pod.SKU
    ) AS QtyCount
FROM tblPODetail pod 

所以，我正在寻找一种更好的方法来过滤只有6位数字开头为第二位（在P或任何字符之后）的PONum的tblVendorShippingInfo表中的记录，忽略所有带有字符的记录（例如PABC123、XYZ123、GHEHEH），然后对其进行索引，以便与tblPODetail表连接。我尝试创建一个带有过滤器的索引视图，但当我使用此视图运行查询时，它失败了。

Alter VIEW vw_tblVendorShippingInfo WITH SCHEMABINDING AS
select dfID, substring(PONum,2, 6) AS POID
, vSKU, ShipQuantity
FROM dbo.tblVendorShippingInfo
WHERE 1 = 1
AND PONum like '%[^0-9]%'

任何建议都将不胜感激。

- Milacay

---

你可能可以通过修改WHERE从句来达到此目的，而不会使它变得特别缓慢：WHERE PONum LIKE CONCAT('_', pod.POID)。这样做的好处是，你不需要对PONum进行子字符串连接，而且我相信你应该能够使用在PONum和POID上拥有的任何索引。 - ZLK

我尝试了这个，但出现了错误：“'CONCAT'不是一个被识别的内置函数名称。” 我正在使用SQL Server 2008。 - Milacay

嗯。LIKE '_' + CAST(pod.POID AS VARCHAR(10)) 与 CONCAT 的效果相同。问题不应该是转换，而是您正在连接 PONum 的子字符串。如果仍然很慢，那么您的视图解决方案可能是最好的。 - ZLK

这会有影响吗？WHERE CONVERT(int, substring(PONum, 2, 6)) = pod.POID - artm

我尝试了"WHERE CONVERT(int, substring(PONum, 2, 6)) = pod.POID"，但它没有起作用。问题在于PONum不一致，有时候值是"XYZ12345"，这样转换就会失败。感谢您的建议。 - Milacay

Answer 1

我认为你正在正确的道路上。你只需要找到合适的表达方式来表示“like”：

SELECT POID, SKU,
       (SELECT SUM(ShipQuantity) AS ShipQuantity 
        FROM tblVendorShippingInfo vsi
        WHERE substring(vsi.PONum, 2, 6) = Convert(varchar(10), pod.POID) AND
              vSKU = pod.SKU
       ) as QtyCount
FROM tblPODetail pod 
WHERE PONum like '[A-Za-z][0-9][0-9][0-9][0-9][0-9][0-9]';

如果您想将其索引为整数，则使用计算列：

alter table tblVendorShippingInfo add column POID_num as try_convert(int, substring(PONum, 2, 6);

create index idx_tblVendorShippingInfo_POID on tblVendorShippingInfo(POID_num);

try_convert() 假设使用的是 SQL Server 2012+。在较早版本的 SQL Server 中，您也可以使用类似的方法。

- Gordon Linoff

---

我认为你的解决方案是最好的，但我仍然在努力理解。关于第一个问题，正确的表达式是否可以仅匹配从第2到第7个位置的字符，但如果它包含字母（A-Z），则不包括任何内容。例如，它将捕获字符串“P123455，Z334545H，9222222KK”中的6位数字记录，但会忽略像“AB12345689，12345Z6”这样的字符串，因为位置2-7包含字母。谢谢。 - Milacay

我也喜欢你提出的第二种计算列解决方案，但我仍然无法弄清楚。我正在使用SQL Server 2008，没有try_convert()选项，计算列能否适用于我上述描述的条件——仅从位置2-7获取6位数字并忽略任何包含字母（A-Z）的内容？ - Milacay

我尝试创建计算列，像这样：""Alter table tblVendorShippingInfo add POID as (Substring(PONum,2, 6) NOT LIKE '%[^0-9]%')""，但是没有起作用。 - Milacay

根据您的建议，我成功地使用SCHEMABINDING函数添加了一个带有索引的计算列。 - Milacay

Answer 2

如果是 Oracle（也许还有其他数据库），您可以在虚拟/计算列上指定索引。

或者，在每晚的上传期间添加一个列并填充它一次。

- McMurphy