我写了一个语句:select a.sl as 数量,b.dj as 单价
from a inner join b on a.id = b.id这样写没有问题,我想求总价,我这么谢提示变量不存在:select a.sl as 数量,b.dj as 单价,(数量 * 单价)as 总价
from a inner join b on a.id = b.id这是为什么! 难道必须这么些么 那太麻烦了:select a.sl as 数量,b.dj as 单价,(a.sl * b.dj) as 总价
from a inner join b on a.id = b.id
from a inner join b on a.id = b.id这样写没有问题,我想求总价,我这么谢提示变量不存在:select a.sl as 数量,b.dj as 单价,(数量 * 单价)as 总价
from a inner join b on a.id = b.id这是为什么! 难道必须这么些么 那太麻烦了:select a.sl as 数量,b.dj as 单价,(a.sl * b.dj) as 总价
from a inner join b on a.id = b.id
select Distinct htgy.strContractID as 合同编号,kh.cCusName as 客户名称,htgy.strContractName as 合同名称,fkjh.dtPayDate as 计划付款日期,fkjh.dblPayRatio as 计划付款比例,fkjh.dblPayCurrency as 计划付款金额,
已付款金额合计 = (case when exists(select * from ap_closebill where Ap_CloseBill.cContractID = htgy.strContractID) then (select sum(iAmount_f) from Ap_CloseBill where Ap_CloseBill.cContractID = htgy.strContractID) else 0 end),
可分摊金额 = (case when (case when exists(select * from ap_closebill where Ap_CloseBill.cContractID = htgy.strContractID) then (select sum(iAmount_f) from Ap_CloseBill where Ap_CloseBill.cContractID = htgy.strContractID) else 0 end) = 0
then 0 else (case when exists(select * from ap_closebill where Ap_CloseBill.cContractID = htgy.strContractID) then (select sum(iAmount_f) from Ap_CloseBill where Ap_CloseBill.cContractID = htgy.strContractID) else 0 end) -
2 end)
from CM_Contract_B as htgy inner join CM_Contract_Pay as fkjh on htgy.strContractID = fkjh.strContractID left join Ap_CloseBill as skd on
fkjh.strContractID = skd.cContractID inner join Customer as kh on htgy.strBisectionUnit = kh.cCusCode
ORDER BY htgy.strContractID,fkjh.dtPayDate语句会很多很不好看懂
写成这样“select a.sl as 数量,b.dj as 单价,(数量 * 单价)as 总价 ”,
“(数量 * 单价)as 总价”里面的“数量”和“单价”被认为是未定义的。
select f1,f2,sum(很复杂的表达式) fa from tb
group by f1,f2
where sum(很复杂的表达式)>100才烦呢:
sum(很复杂的表达式)必须写2遍,每次修改时,2个地方都要一起进行,很容易错漏
这样写是好一点,但是多了一层
select *
from (
select f1,f2,sum(很复杂的表达式) fa from tb
group by f1,f2
) a
where fa>100