从开发那里要来了一段 SQL，表示看不懂……

[KagamineLenKai2]

SELECT CASE a.send_underlying
WHEN '1' THEN '已放标'
WHEN '2' THEN '未放标'
ELSE ''
END
AS isSendUnderlying,
'' isSettleReloan,
CASE a.is_ex_apply
WHEN '1' THEN '新增'
WHEN '2' THEN '展期'
ELSE ''
END
AS isExApply,
ad.marketing_manager AS marketingManager,
ad.sales_name AS salesName,
a.org_city_name AS orgCityName,
c.contract_number AS contractNumber,
a.product_name AS productName,
a.client_name AS clientName,
a.id_number AS idNumber,
c.loan_amount AS loanAmount,
cp.surplus_principal AS surplusPrincipal,
CONCAT(a.loan_term, '-', cp.period) AS loanTerm,
a.service_rates AS serviceRates,
cp.front_service_charge AS frontServiceCharge,
a.product_rates AS productRates,
cp.amortized_interest AS amortizedInterest,
cp.period_money AS periodMoney,
CASE
WHEN a.is_ex_apply = 1 AND cp.period = 0 THEN c.actual_amount
ELSE ''
END
AS actualAmount,
c.actual_loan_time AS actualLoanTime,
<!-- CASE
WHEN a.is_ex_apply = 2 AND cp.period = 0 THEN ctemp.minTime
ELSE ''
END
AS minTime, -->
case
when a.is_ex_apply=1 and cp.period>1 then DATE_SUB(cp.repay_date,INTERVAL 30 Day)
when a.is_ex_apply=2 then DATE_SUB(cp.repay_date ,INTERVAL 30 Day) else '--'
end as minTime,
cp.repay_date AS repayDate,
cp.amortized_principal AS amortizedPrincipal,
'' AS sjhkTime,
'' AS actualPaymentAmount,
'' AS actualSettlementAmount,
CASE WHEN a.zExpStandar = 1 THEN ctemp.minTime ELSE '' END
AS hgZqContinueExTime,
'' AS oweBalance,
'' AS defaultTime,
'' AS defaultDays
FROM contract_plan cp
INNER JOIN apply a ON a.apply_id = cp.apply_id
INNER JOIN apply_detail ad ON ad.apply_id = cp.apply_id
INNER JOIN contract c ON c.apply_id = cp.apply_id
LEFT JOIN
( SELECT min(cpt.repay_date) AS minTime,
max(cpt.repay_date) AS maxTime,
cpt.apply_id
FROM contract_plan cpt
GROUP BY cpt.apply_id) ctemp
ON ctemp.apply_id = cp.apply_id
INNER JOIN node_record n
ON a.apply_id = n.apply_id
AND n.node_code = 'END'
AND n.is_in_node <![CDATA[<>]]> 1

INNER JOIN node_record nr
ON a.apply_id = nr.apply_id
AND nr.node_code = 'loan'
AND nr.status_code <![CDATA[>=]]> 10011200
AND nr.status_code <![CDATA[<=]]> 10011700
WHERE 1 = 1
<isNotEmpty prepend="and" property="orgAuth">
a.org_id in ($orgAuth$)
</isNotEmpty>
<isNotEmpty prepend="and" property="orgid">
a.org_id =#orgid#
</isNotEmpty>
<isNotEmpty prepend="and" property="clientName">
a.client_name =#clientName#
</isNotEmpty>
<isNotEmpty prepend="and" property="isExApply">
a.is_ex_apply = #isExApply#
</isNotEmpty>
<isNotEmpty prepend="and" property="repayDateBegin">
date_format(cp.repay_date,'%Y-%m-%d %h:%i:%s') <![CDATA[>=]]> #repayDateBegin#
</isNotEmpty>
<isNotEmpty prepend="and" property="repayDateEnd">
date_format(cp.repay_date,'%Y-%m-%d %h:%i:%s') <![CDATA[<=]]> #repayDateEnd#
</isNotEmpty>
ORDER BY a.client_name, c.contract_number, cp.repay_date desc
</select>

只会 SELECT 、 FROM 、 INNER INTO 、 LIMIT 的弱鸡表示跪了……

[KagamineLenKai2]

前半部分还好……函数什么的查一查也就知道用法了，不过结尾的那一串
<isNotEmpty prepend="and" property="orgAuth">
a.org_id in ($orgAuth$)
</isNotEmpty>
<isNotEmpty prepend="and" property="orgid">
a.org_id =#orgid#
</isNotEmpty>
<isNotEmpty prepend="and" property="clientName">
a.client_name =#clientName#
</isNotEmpty>
<isNotEmpty prepend="and" property="isExApply">
a.is_ex_apply = #isExApply#
</isNotEmpty>
<isNotEmpty prepend="and" property="repayDateBegin">
date_format(cp.repay_date,'%Y-%m-%d %h:%i:%s') <![CDATA[>=]]> #repayDateBegin#
</isNotEmpty>
<isNotEmpty prepend="and" property="repayDateEnd">
date_format(cp.repay_date,'%Y-%m-%d %h:%i:%s') <![CDATA[<=]]> #repayDateEnd#
</isNotEmpty>
ORDER BY a.client_name, c.contract_number, cp.repay_date desc
</select>

这是 SQL 吗？

[woshihuzios123]

@KagamineLenKai2 百度了一下发现是 ibatis

[yanyuan2046]

看着像 P2P

[KagamineLenKai2]

@woshihuzios123 表示…还是不懂…

[est]

还行。。。请搜索 纯 SQL 实现 MD5 算法。

[Durandal01]

@KagamineLenKai2 这是 mybatis 配置文件里的写法，后面那段是拼接 SQL 用的。

<isNotEmpty>里包着的部分表示在某个参数不为空（ property 所指的参数）的情况下，加上这一段 SQL ，连接字用 prepend 里写的那个。

举例说就是：

<isNotEmpty prepend="and" property="orgAuth">
a.org_id in ($orgAuth$)
</isNotEmpty>

表示 orgAuth 不为空 的情况下， SQL 语句里拼上 and a.org_id in 'orgAuth 的值'

[woshihuzios123]

@KagamineLenKai2 其实我也不懂，我只会 lambda

[lianyue]

这 是我见过最长的 sql 查询 语句了

[daweilv]

弱弱的问一下，这样的统计效率比放在程序里算高吗？

[oscarzhao]

explain 一下看看性能如何

[server]

为了实现而实现，我死之后那管他洪水滔天。

[subpo]

@daweilv 高太多了

[odirus]

@daweilv

从单次查询来讲效率还可以，不过不利于后期优化，拆分成多个查询之后可以有针对性地进行缓存以及其他优化。

[8bit]

子查询比多次 Join 效率要高吧

[arist]

以前做 BI 统计，dba 写单个统计点 SQL，每一项都是一页，大概有几百个统计项。