有关sql查询重复使用的问题

Question

看一下这样的语句行不行。

SELECT 
IF(loan.custType = 0,cu.name,ecu.name) AS 借款人,
ROUND(loan.appAmount / 10000,2) AS 贷款本金（万元）, 
loan.rate AS 实际利率, 
ROUND(CASE loan.rateType 
WHEN 1 THEN loan.rate * 12 
WHEN 2 THEN loan.rate * 12 * 30 
ELSE loan.rate END,2) AS 年化利率, 
amo.rat AS 当月实收利息, 
(SELECT com.commission 
FROM fc_commission AS com 
WHERE com.isenabled = 1 
ORDER BY com.id DESC LIMIT 1) AS 提成率, 
当月实收利息 * 提成率 / 100 AS 当月应计提成
FROM fc_amountrecords AS amo 
LEFT JOIN fc_loancontract AS loan  ON amo.contractId = loan.id 
LEFT JOIN crm_customerinfo AS cu ON cu.id = loan.customerId 
LEFT JOIN crm_ecustomer AS ecu ON ecu.id = loan.customerId;

要达到“当月应计提成”的效果需要怎样做？

Answer 1

你这不行。不知道你用的是什么数据库，MS SQL SERVER和MY SQLSELECT里不能用IF，需要用CASE。另外，语句里除了在ORDER BY能用SELECT里的别名外，不能直接在其他地方用别名引用。你可以把下面的部分提取出来，剩余的部分作用From部分，把提取出来的用作SELECT，当然你可以加上新的From的其他字段。
(SELECT com.commission
FROM fc_commission AS com
WHERE com.isenabled = 1
ORDER BY com.id DESC LIMIT 1) AS 提成率,
当月实收利息 * 提成率 / 100 AS 当月应计提成