是在 FROM t_ICItemCore RIGHT OUTER JOIN t_ICItemBase ON t_ICItemCore.FItemID = t_ICItemBase.FItemID LEFT OUTER JOIN ICInventory ON t_ICItemBase.FItemID = ICInventory.FItemID 后面加上 LEFT( t_ICItemCore.FNumber ,2)='01' 这段代码吗?我想问一下我这样写为什么查不出来
SELECT t_ICItemCore.FNumber AS 物料代码, t_ICItemCore.FName AS 物料名称, t_ICItemCore.FModel AS 规格型号, t_ICItemBase.FSecInv AS 安全库存, SUM(ICInventory.FQty) as 实际库存, SUM(ICInventory.FQty)-convert(float,t_ICItemBase.FSecInv) As 差别, t_ICItemCore.FItemID AS 物料ID FROM t_ICItemCore RIGHT OUTER JOIN t_ICItemBase ON t_ICItemCore.FItemID = t_ICItemBase.FItemID LEFT OUTER JOIN ICInventory ON t_ICItemBase.FItemID = ICInventory.FItemID group by t_ICItemCore.FItemID,t_ICItemCore.FModel,t_ICItemCore.FName,t_ICItemCore.FNumber,t_ICItemBase.FSecInv having t_ICItemCore.FNumber like '01%' and t_ICItemBase.FSecInv >0 and SUM(ICInventory.FQty) <=t_ICItemBase.FSecInv order by 物料代码 改成这样试下
哈哈,比较笨的办法。 select t1.* from (SELECT t_ICItemCore.FNumber AS 物料代码, t_ICItemCore.FName AS 物料名称, t_ICItemCore.FModel AS 规格型号, t_ICItemBase.FSecInv AS 安全库存, SUM(ICInventory.FQty) as 实际库存, SUM(ICInventory.FQty)-convert(float,t_ICItemBase.FSecInv) As 差别, t_ICItemCore.FItemID AS 物料ID FROM t_ICItemCore RIGHT OUTER JOIN t_ICItemBase ON t_ICItemCore.FItemID = t_ICItemBase.FItemID LEFT OUTER JOIN ICInventory ON t_ICItemBase.FItemID = ICInventory.FItemID group by t_ICItemCore.FItemID,t_ICItemCore.FModel,t_ICItemCore.FName,t_ICItemCore.FNumber,t_ICItemBase.FSecInv having t_ICItemBase.FSecInv >0 and SUM(ICInventory.FQty) <=t_ICItemBase.FSecInv )t1,t_icitem t2 where t1.物料代码=t2.fnumber and t2.fnumber like '01.%' order by t1.物料代码
FROM t_ICItemCore
RIGHT OUTER JOIN t_ICItemBase ON t_ICItemCore.FItemID = t_ICItemBase.FItemID
LEFT OUTER JOIN ICInventory ON t_ICItemBase.FItemID = ICInventory.FItemID 后面加上 LEFT( t_ICItemCore.FNumber ,2)='01' 这段代码吗?我想问一下我这样写为什么查不出来
t_ICItemCore.FName AS 物料名称,
t_ICItemCore.FModel AS 规格型号,
t_ICItemBase.FSecInv AS 安全库存,
SUM(ICInventory.FQty) as 实际库存,
SUM(ICInventory.FQty)-convert(float,t_ICItemBase.FSecInv) As 差别,
t_ICItemCore.FItemID AS 物料ID
FROM t_ICItemCore
RIGHT OUTER JOIN t_ICItemBase ON t_ICItemCore.FItemID = t_ICItemBase.FItemID
LEFT OUTER JOIN ICInventory ON t_ICItemBase.FItemID = ICInventory.FItemID
group by t_ICItemCore.FItemID,t_ICItemCore.FModel,t_ICItemCore.FName,t_ICItemCore.FNumber,t_ICItemBase.FSecInv
having t_ICItemCore.FNumber like '01%' and t_ICItemBase.FSecInv >0 and SUM(ICInventory.FQty) <=t_ICItemBase.FSecInv
order by 物料代码 改成这样试下
select t1.* from
(SELECT t_ICItemCore.FNumber AS 物料代码,
t_ICItemCore.FName AS 物料名称,
t_ICItemCore.FModel AS 规格型号,
t_ICItemBase.FSecInv AS 安全库存,
SUM(ICInventory.FQty) as 实际库存,
SUM(ICInventory.FQty)-convert(float,t_ICItemBase.FSecInv) As 差别,
t_ICItemCore.FItemID AS 物料ID
FROM t_ICItemCore
RIGHT OUTER JOIN t_ICItemBase ON t_ICItemCore.FItemID = t_ICItemBase.FItemID
LEFT OUTER JOIN ICInventory ON t_ICItemBase.FItemID = ICInventory.FItemID
group by t_ICItemCore.FItemID,t_ICItemCore.FModel,t_ICItemCore.FName,t_ICItemCore.FNumber,t_ICItemBase.FSecInv
having t_ICItemBase.FSecInv >0 and SUM(ICInventory.FQty) <=t_ICItemBase.FSecInv )t1,t_icitem t2
where t1.物料代码=t2.fnumber and t2.fnumber like '01.%'
order by t1.物料代码