sql语句求助！

[pfworld]

做一个对比统计

A、粮食类型表
B、入库表
C、出库表

现在想实现统计一定时间内的粮食交易对比、但是出现的结果不是我要的，我想实现所有粮食类型的统计，例如：

粮食类型 入库交易次数 出库交易次数 ...

1等玉米 1 1
1等小麦 1 0
2等玉米 0 3
2等小麦 0 0

即使有些类型没有产生出库、入库数据结果为0即可。通过一下代码实现，发现带入条件后结果为只显示有数据的结果，无数据的类型不显示。

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

[shoppo0505]

把where中的两个时间判断拿到各自的left join里面，用and连接就可以。

[dmT4228]

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 出库交易次数

[Rotel-刘志东]

case when 判断一下出库不为空的就为1，否则为0.然后count(*) 具体的交易次数。

[pfworld]

笨办法搞定，借鉴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`

[liurui1985]

楼主的意思是 1等小麦 1 0 2等玉米 0 3 2等小麦 0 0 这几条数据不出来吗？ 如果是这样的话，将检索条件放到leftjoin的on条件里面。 假如入库表里没有对应的数据，left join后，S_In.inGW_DateTime = null。 在通过where条件筛选的话，就会将不存在的数据(S_In.inGW_DateTime = null)去掉 出库也是如此。

[pfworld]

引用 2 楼 dmT4228 的回复:

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 `入库交易次数`

[pfworld]

引用 4 楼 hackervip1988 的回复:

有需要 这么多括号吗

视图制作时，系统带的。

[hackervip1988]

有需要 这么多括号吗