sql - 使用 Case 语句在 T SQL 查询中接收重复项

Question

我创建了以下 case 语句来根据商品销售计量单位进行一些乘法运算，对于此示例，即使我将 SalesUoM 变量声明为加仑，它也会对加仑和磅进行数学运算。

 declare @SalesUOM as nvarchar(30)
 declare @drumqty as nvarchar(30)
 Set @SalesUOM = 'GAL'
 Set @drumqty = '3'

 SELECT DISTINCT 
 Case
 --- when @SalesUOM = 'DRUM' then ( @drumqty * T1.BaseQty ) 
 -- when @SalesUOM   = 'PALLET' then ( @drumqty * T4.BaseQty )
 --when @SalesUOM = 'PAIL' then ( @drumqty * T6.BaseQty )
 --when @SalesUOM  = 'TOTE' then ( @drumqty * T8.BaseQty )
 when @SalesUOM   = 'LB' then ( @drumqty * T10.BaseQty )
 When @SalesUOM = 'GAL' then (@drumQty * T12.BaseQty)
 Else '0' End as 'Item Qty'


 FROM OUGP T0  
 --INNER JOIN UGP1 T1 ON T0.[UgpEntry] = T1.[UgpEntry] 
 --INNER JOIN UGP1 T4 on T0.[UgpEntry] = T4.[UgpEntry]
 --INNER JOIN UGP1 T6 on T0.[UgpEntry] = T6.[UgpEntry]
 --INNER JOIN UGP1 T8 on T0.UgpEntry = T8.UgpEntry
 inner JOIN UGP1 T10 on T0.UgpEntry = T10.UgpEntry 
 inner JOIN UGP1 T12 on T0.UgpEntry = T12.UgpEntry
 --INNER JOIN OUOM T2 ON T0.[BaseUom] = T2.[UomEntry]
 --Inner Join OUOM T3 on T1.[UomEntry] = T3.[UomEntry] AND T3.UOMCODE in ('DR-15', 'DR-30', 'DR-55')
 --Inner Join OUOM T5 on T4.[UomEntry] = T5.[UomEntry] and T5.UOMCODE = 'PALLET'
 --left Join OUOM T7 on T6.UoMEntry = T7.UomEntry and T7.UOMCODE = 'PAIL-5'
 --left Join OUOM T9 on T8.UomEntry = T9.UomEntry AND T9.UomCode = 'TOTE'
 Left Join OUOM T11 on T10.UomEntry = T11.UomEntry and T11.UomCode = 'LB'
 Left Join OUOM T13 on T12.UomEntry = T13.UomEntry and T13.UomCode = 'GAL'
 Left join OITM on T0.UgpCode = OITM.ItemCode

 WHERE  
   OITM.ItemCode = '0000000'

标签： sql

Answer 1

因为您没有在 where 子句或内部联接中过滤 @SalesUOM 上的结果集。T10 和 T12 指向同一个表 UGP1。

以下是可以解决您的问题的两种方法 1. 在 where 子句中添加 T11.UomCode = @SalesUOM 和 T13.UomCode = @SalesUOM 2. 替换以下语句“Left Join OUOM T11 on T10.UomEntry = T11.UomEntry and T11.UomCode = 'LB' 在 T12.UomEntry = T13.UomEntry 和 T13.UomCode = 'GAL' 上左加入 OUOM T13"

 with below statements

 Inner Join OUOM T11 on T10.UomEntry = T11.UomEntry and T11.UomCode = @SalesUOM