如果换成统计次数就出现计算错误,这个为什么呀。 count(case when S_In.inIDis not null then 1 else 0 end) as 出库交易次数 换成独立运算就好着 (select count(`in1`.`inID`) AS `count(in1.inID)` from `S_In` `in1` where (`in1`.`fkFoodTypeID` = `Z_FoodType`.`foodTypeID`)) AS `入库交易次数`
笨办法搞定,借鉴shoppo0505提议! select `Z_FoodType`.`foodTypeName` AS `粮食类型`, (select count(`in1`.`inID`) AS `count(in1.inID)` from `S_In` `in1` where (`in1`.`fkFoodTypeID` = `Z_FoodType`.`foodTypeID` AND `in1`.`inGW_DateTime` BETWEEN '2013-06-25 00:00:00' AND '2013-06-25 23:59:59')) AS `入库交易次数`, (select count(`out1`.`outID`) AS `count(out1.outID)` from `S_Out` `out1` where (`out1`.`fkFoodTypeID` = `Z_FoodType`.`foodTypeID` AND `out1`.`outTW_DateTime` BETWEEN '2013-06-25 00:00:00' AND '2013-06-25 23:59:59')) AS `出库交易次数`, sum(`S_In`.`inBuckleWeight`) AS `入库总扣杂量(KG)`, sum(`S_Out`.`outBuckleWeight`) AS `出库总扣杂量(KG)`, sum(`S_In`.`inNW_Real`) AS `入库总重量(KG)`, sum(`S_Out`.`outNW_Real`) AS `出库总重量(KG)`, min(`S_In`.`inFinalPrice`) AS `入库最小单价(元/KG)`, min(`S_Out`.`outPrice`) AS `出库最小单价(元/KG)`, avg(`S_In`.`inFinalPrice`) AS `入库平均单价(元/KG)`, avg(`S_Out`.`outPrice`) AS `出库平均单价(元/KG)`, max(`S_In`.`inFinalPrice`) AS `入库最高单价(元/KG)`, max(`S_Out`.`outPrice`) AS `出库最高单价(元/KG)`, sum(`S_In`.`inMoney_SP`) AS `入库总金额(元)`, sum(`S_Out`.`outMoney_SP`) AS `出库总金额(元)` from ((`Z_FoodType` left join `S_In` on((`Z_FoodType`.`foodTypeID` = `S_In`.`fkFoodTypeID`)) AND `S_In`.`inGW_DateTime` BETWEEN '2013-06-25 00:00:00' AND '2013-06-25 23:59:59') left join `S_Out` on((`Z_FoodType`.`foodTypeID` = `S_Out`.`fkFoodTypeID`)) AND `S_Out`.`outTW_DateTime` BETWEEN '2013-06-25 00:00:00' AND '2013-06-25 23:59:59') group by `Z_FoodType`.`foodTypeName`
sum(case when S_Out.fkFoodTypeID is not null then 1 else 0 end) as 出库交易次数
具体的交易次数。
1等小麦 1 0
2等玉米 0 3
2等小麦 0 0
这几条数据不出来吗?
如果是这样的话,将检索条件放到leftjoin的on条件里面。
假如入库表里没有对应的数据,left join后,S_In.inGW_DateTime = null。
在通过where条件筛选的话,就会将不存在的数据(S_In.inGW_DateTime = null)去掉
出库也是如此。
count(case when S_In.inIDis not null then 1 else 0 end) as 出库交易次数
换成独立运算就好着
(select count(`in1`.`inID`) AS `count(in1.inID)` from `S_In` `in1` where
(`in1`.`fkFoodTypeID` = `Z_FoodType`.`foodTypeID`)) AS `入库交易次数`
select `Z_FoodType`.`foodTypeName` AS `粮食类型`,
(select count(`in1`.`inID`) AS `count(in1.inID)` from `S_In` `in1`
where
(`in1`.`fkFoodTypeID` = `Z_FoodType`.`foodTypeID`
AND
`in1`.`inGW_DateTime` BETWEEN '2013-06-25 00:00:00' AND '2013-06-25 23:59:59')) AS `入库交易次数`,
(select count(`out1`.`outID`) AS `count(out1.outID)` from `S_Out` `out1`
where
(`out1`.`fkFoodTypeID` = `Z_FoodType`.`foodTypeID`
AND
`out1`.`outTW_DateTime` BETWEEN '2013-06-25 00:00:00' AND '2013-06-25 23:59:59')) AS `出库交易次数`,
sum(`S_In`.`inBuckleWeight`) AS `入库总扣杂量(KG)`,
sum(`S_Out`.`outBuckleWeight`) AS `出库总扣杂量(KG)`,
sum(`S_In`.`inNW_Real`) AS `入库总重量(KG)`,
sum(`S_Out`.`outNW_Real`) AS `出库总重量(KG)`,
min(`S_In`.`inFinalPrice`) AS `入库最小单价(元/KG)`,
min(`S_Out`.`outPrice`) AS `出库最小单价(元/KG)`,
avg(`S_In`.`inFinalPrice`) AS `入库平均单价(元/KG)`,
avg(`S_Out`.`outPrice`) AS `出库平均单价(元/KG)`,
max(`S_In`.`inFinalPrice`) AS `入库最高单价(元/KG)`,
max(`S_Out`.`outPrice`) AS `出库最高单价(元/KG)`,
sum(`S_In`.`inMoney_SP`) AS `入库总金额(元)`,
sum(`S_Out`.`outMoney_SP`) AS `出库总金额(元)`
from
((`Z_FoodType`
left join `S_In` on((`Z_FoodType`.`foodTypeID` = `S_In`.`fkFoodTypeID`))
AND
`S_In`.`inGW_DateTime` BETWEEN '2013-06-25 00:00:00' AND '2013-06-25 23:59:59')
left join `S_Out` on((`Z_FoodType`.`foodTypeID` = `S_Out`.`fkFoodTypeID`))
AND
`S_Out`.`outTW_DateTime` BETWEEN '2013-06-25 00:00:00' AND '2013-06-25 23:59:59')
group by `Z_FoodType`.`foodTypeName`