SQL Server - 连接 VARCHAR 和 INT 的更好方法

Question

我正在寻找加快查询速度的有效方法。让我描述一下情况。

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

tblVendorShippingInfo(请注意，下面所有这些列都是 VARCHAR，因为有时即使在 QtyShipped 中报告也包含 Varchar 字符)

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 

所以，我正在寻找一种更好的方法来过滤 tblVendorShippingInfo 表中的记录，该表中只有从第二位开始的 6 位 PONum(在 P 或任何字符之后)，忽略所有带字符的记录(例如 PABC123、XYZ123 , GHEHEH)，然后对其进行索引，以便它可以用于与 tblPODetail 表的连接。

我试图用过滤器创建一个索引 View ，但是当我用这个 View 运行查询时，它失败了。

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]%'

如有任何建议，我们将不胜感激。

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 执行类似的操作。