SELECT
Z_FoodType.foodTypeName AS `粮食类型`,
(select count(`in1`.`inID`) AS `count(in1.inID)` from `S_In` `in1` where
(`in1`.`fkFoodTypeID` = `Z_FoodType`.`foodTypeID`)) AS `入库交易次数`,
(select count(`out1`.`outID`) AS `count(out1.outID)` from `S_Out` `out1` where
(`out1`.`fkFoodTypeID` = `Z_FoodType`.`foodTypeID`)) AS `出库交易次数`,
Sum(S_In.inNW_Real) AS `入库总重量`,
Sum(S_Out.outNW_Real) AS `出库总重量`,
Min(S_In.inFinalPrice) AS `入库最小单价`,
Min(S_Out.outPrice) AS `出库最小单价`,
Avg(S_In.inFinalPrice) AS `入库平均单价`,
Avg(S_Out.outPrice) AS `出库平均单价`,
Max(S_In.inFinalPrice) AS `入库最高单价`,
Max(S_Out.outPrice) AS `出库最高单价`,
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`))) left join `S_Out` on((`Z_FoodType`.`foodTypeID` =
`S_Out`.`fkFoodTypeID`)))
WHERE
`S_In`.`inGW_DateTime` BETWEEN '2013-01-01 00:00:00' AND '2013-12-31 23:59:59'
AND
`S_Out`.`outTW_DateTime` BETWEEN '2013-01-01 00:00:00' AND '2013-12-31 23:59:59'
GROUP BY
Z_FoodType.foodTypeName
sum(case when S_In.fkFoodTypeID is not null then 1 else 0 end) as 入库交易次数
sum(case when S_Out.fkFoodTypeID is not null then 1 else 0 end) as 出库交易次数
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`
