我现在用的是这种方法,还有不有更好的呢? SELECT * FROM (SELECT tStrikeBalance.Stb_MaterialID, ROUND( tStrikeBalance.Stb_TagBalance, 2 ) as StrikeBalance FROM tStrikeBalance ORDER BY tStrikeBalance.Stb_MaterialID ) as Balance, (SELECT tMaterial.Mtl_ID, tMaterial.Mtl_NO, tMaterial.Mtl_Unit, tMaterial.Mtl_Name, tMaterial.Mtl_Category, tMaterial.Mtl_Model, tMaterial.Mtl_Specification, tMaterial.Mtl_UnitPrice, tMaterial.Mtl_Currency, ROUND( tMaterial.Mtl_Balance * tMaterial.Mtl_UnitPrice, 2 ) as AmountCN, ROUND( tMaterial.Mtl_Balance * tMaterial.Mtl_Currency, 2 ) as AmountHK, tMaterial.Mtl_Balance FROM tMaterial ORDER BY tMaterial.Mtl_ID ) as Material, (SELECT tFormDetail.Frd_MaterialID, ROUND( sum( tFormDetail.Frd_Out ) , 2 ) as AmoutOut, ROUND( sum( tFormDetail.Frd_In ) , 2 ) as AmoutIn FROM tFormDetail GROUP BY tFormDetail.Frd_MaterialID ORDER BY tFormDetail.Frd_MaterialID ) as AmountQty where Balance.Stb_MaterialID = Material.Mtl_ID and Balance.Stb_MaterialID = AmountQty.Frd_MaterialID
因为sum,你的分组也不对
另外,连接时先过滤掉不需要的数据(where ...)
zjcxc(: 邹建 :) ( ) 这个是SQL语句到哪都是样的.
能不能说的具体点..
SELECT *
FROM (SELECT tStrikeBalance.Stb_MaterialID, ROUND( tStrikeBalance.Stb_TagBalance, 2 )
as StrikeBalance
FROM tStrikeBalance
ORDER BY tStrikeBalance.Stb_MaterialID
)
as Balance, (SELECT tMaterial.Mtl_ID, tMaterial.Mtl_NO, tMaterial.Mtl_Unit, tMaterial.Mtl_Name, tMaterial.Mtl_Category, tMaterial.Mtl_Model, tMaterial.Mtl_Specification, tMaterial.Mtl_UnitPrice, tMaterial.Mtl_Currency, ROUND( tMaterial.Mtl_Balance * tMaterial.Mtl_UnitPrice, 2 )
as AmountCN, ROUND( tMaterial.Mtl_Balance * tMaterial.Mtl_Currency, 2 )
as AmountHK, tMaterial.Mtl_Balance
FROM tMaterial
ORDER BY tMaterial.Mtl_ID
)
as Material, (SELECT tFormDetail.Frd_MaterialID, ROUND( sum( tFormDetail.Frd_Out ) , 2 )
as AmoutOut, ROUND( sum( tFormDetail.Frd_In ) , 2 )
as AmoutIn
FROM tFormDetail
GROUP BY tFormDetail.Frd_MaterialID
ORDER BY tFormDetail.Frd_MaterialID
)
as AmountQty
where Balance.Stb_MaterialID = Material.Mtl_ID
and Balance.Stb_MaterialID = AmountQty.Frd_MaterialID
2.inner join
3.left join
4.right join
这四个方法哪一个运行速度更快一点呢?