小弟在写一个报表SQL时候遇到问题,求大家帮忙,下边sql貌似正确,确实在只是判断了=‘按金额’,执行出来结果 10,而按比例还有 2 我想要的结果是12 但是现在结果是 10,想问问如果‘按金额’和‘按比例’同时存在,我如何能够得合计呢?
SELECT Hand_Biller, EmpCode, EmpName,
SUM(Num) AS SumNum, SUM(Total_Money)AS SumTotalMoney,
SUM(NewPrice * Num) AS SumShouldMoney ,
sum(case when gd.DeductMode='按金额' then gd.DeductModeValue*SUM(Num)
when gd.DeductMode='按比例' then SUM(NewPrice * Num)*gd.DeductModeValue/100
else 0 end) as bili) as bili
FROM TB_Consume_Detail
LEFT JOIN Employees ON TB_Consume_Detail.Hand_Biller = Employees.EmpID
LEFT join goodsdeduct gd on gd.goodsid=Goods_id
GROUP BY Hand_Biller, EmpCode, EmpName,gd.DeductModeValue,gd.DeductMode
SELECT Hand_Biller, EmpCode, EmpName,
SUM(Num) AS SumNum, SUM(Total_Money)AS SumTotalMoney,
SUM(NewPrice * Num) AS SumShouldMoney ,
sum(case when gd.DeductMode='按金额' then gd.DeductModeValue*SUM(Num)
when gd.DeductMode='按比例' then SUM(NewPrice * Num)*gd.DeductModeValue/100
else 0 end) as bili) as bili
FROM TB_Consume_Detail
LEFT JOIN Employees ON TB_Consume_Detail.Hand_Biller = Employees.EmpID
LEFT join goodsdeduct gd on gd.goodsid=Goods_id
GROUP BY Hand_Biller, EmpCode, EmpName,gd.DeductModeValue,gd.DeductMode
EmpCode ,
EmpName ,
SUM(SumNum) SumNum ,
SUM(SumTotalMoney) SumTotalMoney ,
SUM(SumShouldMoney) SumShouldMoney ,
SUM(bili) bili
FROM ( SELECT Hand_Biller ,
EmpCode ,
EmpName ,
SUM(Num) AS SumNum ,
SUM(Total_Money) AS SumTotalMoney ,
SUM(NewPrice * Num) AS SumShouldMoney ,
gd.DeductModeValue * SUM(Num) AS bili
--WHEN gd.DeductMode = '按比例'
--THEN SUM(NewPrice * Num) * gd.DeductModeValue / 100
--ELSE 0
--END AS bili
FROM TB_Consume_Detail
LEFT JOIN Employees ON TB_Consume_Detail.Hand_Biller = Employees.EmpID
LEFT JOIN goodsdeduct gd ON gd.goodsid = Goods_id
WHERE gd.DeductMode = '按金额'
GROUP BY Hand_Biller ,
EmpCode ,
EmpName ,
gd.DeductModeValue ,
gd.DeductMode
UNION ALL
SELECT Hand_Biller ,
EmpCode ,
EmpName ,
SUM(Num) AS SumNum ,
SUM(Total_Money) AS SumTotalMoney ,
SUM(NewPrice * Num) AS SumShouldMoney ,
SUM(NewPrice * Num) * gd.DeductModeValue / 100 AS bili
FROM TB_Consume_Detail
LEFT JOIN Employees ON TB_Consume_Detail.Hand_Biller = Employees.EmpID
LEFT JOIN goodsdeduct gd ON gd.goodsid = Goods_id
WHERE gd.DeductMode = '按比例'
GROUP BY Hand_Biller ,
EmpCode ,
EmpName ,
gd.DeductModeValue ,
gd.DeductMode
) a
GROUP BY Hand_Biller ,
EmpCode ,
EmpName
SUM(Num) AS SumNum, SUM(Total_Money)AS SumTotalMoney,
SUM(NewPrice * Num) AS SumShouldMoney ,
sum(case when gd.DeductMode='按金额' then gd.DeductModeValue*Num
when gd.DeductMode='按比例' then NewPrice * Num*gd.DeductModeValue/100
else 0 end) as bili
FROM TB_Consume_Detail
LEFT JOIN Employees ON TB_Consume_Detail.Hand_Biller = Employees.EmpID
LEFT join goodsdeduct gd on gd.goodsid=Goods_id
GROUP BY Hand_Biller, EmpCode, EmpName,gd.DeductModeValue,gd.DeductMode