问题描述

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

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

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;

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

解决方案

你这不行。不知道你用的是什么数据库，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 当月应计提成