处理匹配另外一个表 以R,X,T,'R/NOPB','T/NOPB','X/NOPB' 结尾合并统计,即单个R需要匹配另外6个数据,并且需要合计这6个数据
SELECT *, (SELECT SUM(a.QtyLeft) StorageQty from BusiTradeList a
LEFT JOIN BusiTradeMain m on m.TradeNo = a.TradeNo AND m.CompanyCode = 'hsdz'
where m.Status = 2 and a.CompanyCode = 'hsdz' and m.TradeNoType in ('JInS', 'JInDB') and
(t.GoodsModelFact1=a.GoodsModelFact
OR t.GoodsModelFact1+'R'=a.GoodsModelFact
OR t.GoodsModelFact1+'T'=a.GoodsModelFact
OR t.GoodsModelFact1+'X'=a.GoodsModelFact
OR t.GoodsModelFact1+'R/NOPB'=a.GoodsModelFact
OR t.GoodsModelFact1+'X/NOPB'=a.GoodsModelFact
OR t.GoodsModelFact1+'T/NOPB'=a.GoodsModelFact )) StorageQty
FROM
(SELECT b.ModifyCode, b.GoodsModelFact,(CASE
WHEN PATINDEX('%R', b.GoodsModelFact)>0 THEN SUBSTRING(b.GoodsModelFact, 1, PATINDEX('%R', b.GoodsModelFact)-1)
WHEN PATINDEX('%T', b.GoodsModelFact)>0 THEN SUBSTRING(b.GoodsModelFact, 1, PATINDEX('%T', b.GoodsModelFact)-1)
WHEN PATINDEX('%X', b.GoodsModelFact)>0 THEN SUBSTRING(b.GoodsModelFact, 1, PATINDEX('%X', b.GoodsModelFact)-1)
WHEN PATINDEX('%X/NOPB', b.GoodsModelFact)>0 THEN SUBSTRING(b.GoodsModelFact, 1, PATINDEX('%X/NOPB', b.GoodsModelFact)-1)
WHEN PATINDEX('%T/NOPB', b.GoodsModelFact)>0 THEN SUBSTRING(b.GoodsModelFact, 1, PATINDEX('%T/NOPB', b.GoodsModelFact)-1)
WHEN PATINDEX('%R/NOPB', b.GoodsModelFact)>0 THEN SUBSTRING(b.GoodsModelFact, 1, PATINDEX('%R/NOPB', b.GoodsModelFact)-1)
ELSE '' END) GoodsModelFact1
from BusiTradeJXJ b
where b.CompanyCode = 'hsdz' AND b.BrandID in ('TI','Texas Instruments') ) AS T
WHERE t.GoodsModelFact1 <>'' and t.GoodsModelFact='TPS65251RHAR'