sql日常记录（任一个以R,X,T,‘R/NOPB‘,‘T/NOPB‘,‘X/NOPB‘ 结尾的合并统计另外6个）

本文链接：https://blog.youkuaiyun.com/qq_37227093/article/details/119148462

本文介绍了一个复杂的SQL查询案例，用于从两个不同的表中匹配特定的数据模式并进行汇总统计。该查询涉及多个条件判断与字符串操作，旨在针对特定的商品型号进行库存数量的计算。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

				处理匹配另外一个表 以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'