求思路，几十W数据与另一个表8W条，进行联合查询，进行单位换算

[从不签到]

有一个库存变化表A，里面有至少几十W条数据
ItemID TransDate InventUnit PurchUnit SalesUnit Qty CostUnit InventFactor PurchFactor SalesFactor
A001 2014/10/01 kg g kg 100 kg
A001 2014/10/02 kg g kg 200 kg
B001 2014/10/01 kg g kg 100 kg
B001 2014/10/02 kg g kg 200 kg

单位换算表B
ItemID FromUnit ToUnit Factor
A001 kg g 1000
A001 g kg 0.001

现在要分别对A表的 InventUnit ，PurchUnit， SalesUnit 与 CostUnit 对比，相同则=1，不同则到表B去检索，得到 InventFactor PurchFactor SalesFactor

如果单纯通过外联，效率非常低

select  A.ItemId,
case when  A.InventUnit = A.CostUnit then 1 else  B1.Factor end as InventFactor，
case when  A.PurchUnit = A.CostUnit then 1 else  B2.Factor end as PurchFactor，
case when  A.SalesUnit = A.CostUnit then 1 else  B3.Factor end as SalesFactor
from A 
left join B B1
on A.ItemId = B1.ItemID
and A.InventUnit = B1.FromUnit
and A.CostUnit = B1.ToUnit
left join B B2
on A.ItemId = B2.ItemID
and A.InventUnit = B2.FromUnit
and A.CostUnit = B2.ToUnit
left join B B3
on A.ItemId = B3.ItemID
and A.InventUnit = B3.FromUnit
and A.CostUnit = B3.ToUnit

后来想了一下，因为A表ItemId有很多重复，所以想先对A的ItemId进行GroupBy，得到A1表
在跟B进行外联，作为C表 ，然后A跟C内联，得到 3个单位换算系数

我这么想的，各位大大还有什么更好的方法没？

[还在加载中灬]

可以考虑建索引，建议按这个顺序建覆盖索引 A表，ItemId CostUnit B表，ItemID ToUnit FromUnit 然后连接顺序按索引列的顺序 及减少连接次数

select  A.ItemId,
ISNULL(B1.Factor,1)as InventFactor,
ISNULL(B2.Factor,1)as PurchFactor,
ISNULL(B3.Factor,1)as SalesFactor
from A 
left join B B1
on A.ItemId = B1.ItemID AND A.CostUnit<>A.InventUnit
and A.CostUnit = B1.ToUnit
and A.InventUnit = B1.FromUnit
left join B B2
on A.ItemId = B2.ItemID AND A.CostUnit<>A.PurchUnit
and A.CostUnit = B2.ToUnit
and A.PurchUnit = B2.FromUnit
left join B B3
on A.ItemId = B3.ItemID AND A.CostUnit<>A.SalesUnit
and A.CostUnit = B3.ToUnit
and A.SalesUnit = B3.FromUnit

因为你的查询就表关联查询比较多，我想上索引可能会快些

[Tiger_Zhao]

WITH sA AS (--筛选出A中的单位
    SELECT DISTINCT ItemID, InventUnit, PurchUnit, SalesUnit, CostUnit
      FROM A
     WHERE InventFactor IS NULL
        OR PurchFactor IS NULL
        OR SalesFactor IS NULL
)
,sB (--筛选出用到的单位换算
    SELECT B.*
      FROM B
      JOIN sA
        ON B.ItemID = sA.ItemID
       AND B.ToUnit = sA.CostUnit
       AND (   B.FromUnit = sA.InventUnit
            OR B.FromUnit = sA.PurchUnit
            OR B.FromUnit = sA.SalesUnit
           )
)
    UPDATE A
       SET A.InventFactor = ISNULL(b1.Factor,1),
           A.PurchFactor = ISNULL(b2.Factor,1),
           A.SalesFactor = ISNULL(b3.Factor,1)
      FROM A
 LEFT JOIN sB b1
        ON A.ItemID = b1.ItemID
        -- A.ConstUnit 已经在 sB 中过滤过了，不用写关联了。
       AND A.InventUnit = b1.FromUnit
 LEFT JOIN sB b2
        ON A.ItemID = b2.ItemID
       AND A.PurchUnit = b2.FromUnit
 LEFT JOIN sB b3
        ON A.ItemID = b3.ItemID
       AND A.SalesUnit = b3.FromUnit
     WHERE A.InventFactor IS NULL
        OR A.PurchFactor IS NULL
        OR A.SalesFactor IS NULL

可以这样试试，不过最好是插入数据时就加上换算率，否则一次性更新几十W日志都很多，更不要说性能了。

[LongRui888]

这种还是比较难优化的，其实这种情况，应该 在B表多一个主键字段，然后再A表中直接存储找个字段，用来关联，现在是必须要同时用3个字段来关联。 现在运行要多久呢？

[LongRui888]

楼上的也是好方法。 为了减少join的开销，可以考虑冗余字段，也就是直接把 factor放到 库存变化表。

[从不签到]

其实表B是个3个表关联起来的view， 不用view，每次写3个表我觉得麻烦 现在3300条数据，按照我之前的方法，跑了10多分钟没跑完，我就停下了

[Tiger_Zhao]

转换率不应该区分ItemID的吧，1kg=1000g应该是不变的啊！

[从不签到]

表B的构造是这样的 单位换算表B ItemID FromUnit ToUnit Factor Product NULL kg g 1000 0 NULL g kg 0.001 0 A001 kg 袋 2 12345678 像 kg = 1000g等共通的变换， ItemId = NULL， Product = 0 其余的则有ItemID 上面的例子不太好

[Tiger_Zhao]

...
,sB (--筛选出用到的单位换算
    SELECT sA.ItemID,
           ISNULL(B1.FromUnit,B0.FromUnit) FromUnit,
           ISNULL(B1.ToUnit,B0.ToUnit) ToUnit,
           ISNULL(B1.Factor,B0.Factor) Factor
      FROM sA
 LEFT JOIN B1
        ON B1.ItemID = sA.ItemID
       AND B1.ToUnit = sA.CostUnit
       AND (   B1.FromUnit = sA.InventUnit
            OR B1.FromUnit = sA.PurchUnit
            OR B1.FromUnit = sA.SalesUnit
           )
 LEFT JOIN B0
        ON B0.ItemID IS NULL
       AND B0.ToUnit = sA.CostUnit
       AND (   B0.FromUnit = sA.InventUnit
            OR B0.FromUnit = sA.PurchUnit
            OR B0.FromUnit = sA.SalesUnit
           )
)
...

这样应该比先做个全部ItemID的视图数据量小吧。

[从不签到]

B表已经是View了，8w条数据 上面的方法执行的话， 15条数据跑了很长时间，没耐心等，就停了。。。

[Tiger_Zhao]

不要View，去关联原始的转换表啊！！！

[alongkuku]

如果B表的数据是不会变的，建议直接把单位换算率在A表新增、修改数据时带上换算率。如果变化，建议索引连接join即可。

[从不签到]

B的View是由B1，B2组成的 B1保存的是单位，B2保存的是换算率， 通过RecId进行关联的 所以 查询的时候，得到2个表去找，跟view是一样的 建议直接把单位换算率在A表新增、修改数据时带上换算率 这个怎么弄？

[Tiger_Zhao]

A)在你做 INSERT/UPDATE 之前先把换算率查出来，包含在插入/更新中。
B)用触发器，把插入/更新相关行的换算率查出来，更新到记录中。
这样处理的数据量不大，从B1、B2查询的范围也小，速度够快。等于把批量更新的耗时分散到平时的操作中，感觉不到性能的影响。