sql总结及优化

1：case when
sum(case when tao.ORDER_ISBLACK !=’-1’ then 1 else 0 end )as totalCount
当编号不等于-1，为1，否则为0，统计和
2：left
left(tao.ORDER_CITY,6) as code
截取左侧六位
3：DATE_FORMAT
DATE_FORMAT(tao.ORDER_CREATE_TIME,’%Y-%m-%d’) BETWEEN #{startTime} AND #{endTime}
格式化时间为年月日
4：ifnull，SUBSTRING_INDEX
ifnull(SUBSTRING_INDEX(taob.ORDER_BUSINESS_SECTOR,’-’,1),‘其他’) as kind,
截取字段第一个-前面的字符串，如果为空，则取其他
5：DISTINCT
SELECT DISTINCT tani.NDINDUSTRY_FL_NAME as type
去重
6：字段映射


7：分组求和：

SELECT
count(1) totalCount,
sum(b.totalNum) totalNum,
sum(b.totalAmount)/10000 totalAmount
FROM(
SELECT
a.premiumBankCode,
a.premiumBank,
sum(a.totalNum) totalNum,
sum(a.totalAmount) totalAmount
FROM
(
SELECT
i.BRANCH_NAME branchName,
p.PREMIUM_BANK_CODE premiumBankCode,
p.PREMIUM_BANK premiumBank,
count(p.ID) totalNum,
sum(p.PAYMENT_AMOUNT) totalAmount
FROM
t_agriguarantee_premium p
LEFT JOIN (SELECT DISTINCT ORDER_NO, BRANCH_NAME, CONTRACT_NO from t_agriguarantee_loan_info ) i ON p.ORDER_NO = i.ORDER_NO
LEFT JOIN t_agriguarantee_order O ON p.ORDER_NO = O.ORDER_NO
WHERE
1 = 1

AND p.PREMIUM_BANK LIKE CONCAT(’%’,#{bankName},’%’)


AND p.PREMIUM_BANK_CODE = #{premiumBankCode}


AND p.PAYMENT_TIME <![CDATA[>=]]> CONCAT(#{startDate},’ 00:00:00’)


AND p.PAYMENT_TIME <![CDATA[<=]]> CONCAT(#{endDate},’ 23:59:59’)


AND O.ORDER_COUNTY in ${orderCounty}

GROUP BY i.BRANCH_NAME
) a
GROUP BY a.premiumBankCode
) b

8：choose when：

SELECT
p.PAYMENT_TIME,
p.FILE_DATE,
e.LOAN_APPLICANT_NAME,
CASE e.LOAN_APPLICANT_ID = e.LOAN_APPLICANT_IDCARD WHEN 1 THEN e.LOAN_APPLICANT_IDCARD ELSE e.LOAN_APPLICANT_ID END AS LOAN_APPLICANT_IDCARD,
i.CONTRACT_NO,
i.BRANCH_NAME,


(SELECT IFNULL(MIN(DO_PRIV) , ‘0’) FROM t_agriguarantee_priv WHERE COUNTRY_CODE=O.ORDER_COUNTY AND
REGISTER_NAME=#{registerName} LIMIT 0,1) AS doPriv,


‘0’ AS doPriv,




(SELECT IFNULL(MIN(SELECT_PRIV) , ‘0’) FROM t_agriguarantee_priv WHERE COUNTRY_CODE=O.ORDER_COUNTY AND
REGISTER_NAME=#{registerName} LIMIT 0,1) AS selectPriv


‘0’ AS selectPriv


FROM
t_agriguarantee_premium p
LEFT JOIN (SELECT DISTINCT ORDER_NO, BRANCH_NAME, CONTRACT_NO from t_agriguarantee_loan_info ) i ON p.ORDER_NO = i.ORDER_NO
LEFT JOIN t_agriguarantee_order_guarantee e ON p.ORDER_NO = e.ORDER_NO
LEFT JOIN t_agriguarantee_order O on p.ORDER_NO = O.ORDER_NO
where 1=1

AND O.ORDER_COUNTY in ${orderCounty}

ORDER BY p.PAYMENT_TIME DESC
limit #{beginIndex},#{pageSize}

9，cast（类型转换）
CAST(
og.LOAN_GUARANTEE_RATE AS DECIMAL (18, 3)
) * 100 AS loanGuaranteeRate,
10，concat,group_concat,substring
拼接字段、分组拼接字段

SELECT
O.ORDERID,
O.ORDER_NO,
C.CUST_NAME,
C.CUST_IDENTITY_CARD,
(SELECT NAME FROM t_agriguarantee_address WHERE CODE=O.ORDER_CITY LIMIT 0,1) AS ORDER_CITY,
(SELECT NAME FROM t_agriguarantee_address WHERE CODE=O.ORDER_COUNTY LIMIT 0,1) AS ORDER_COUNTY,
(SELECT NAME FROM t_agriguarantee_address WHERE CODE=O.ORDER_TOWN LIMIT 0,1) AS ORDER_TOWN,
C.CUST_HOME_ADDRESS,
O.ORDER_PHONE,
O.ORDER_LOAN_AMOUNT,
CONCAT(H.LOANUSE_DESC,’:’,O.ORDER_LOAN_PURPOSEDESC) AS ORDER_LOAN_PURPOSEDESC,
B.BANK_NAME AS ORDER_BANK,
CONCAT(SUBSTRING(O.ORDER_PREPARIED_TIME,1,4),‘年’,SUBSTRING(O.ORDER_PREPARIED_TIME,5,6),‘月’) AS ORDER_PREPARIED_TIME,
(SELECT GROUP_CONCAT(ORDER_BUSINESS_SECTOR) from t_agriguarantee_order_business where ORDERID=O.ORDERID) as ORDER_BUSINESS_SECTOR,
(SELECT GROUP_CONCAT(ORDER_BUSINESS_SCALENUMS,’’,ORDER_BUSINESS_SCALEUNIT separator ‘,’) from t_agriguarantee_order_business where ORDERID=O.ORDERID) ORDER_BUSINESS_SCALE,
(SELECT GROUP_CONCAT(ORDER_BUSINESS_PERIOD) from t_agriguarantee_order_business where ORDERID=O.ORDERID) as ORDER_BUSINESS_PERIOD,
O.ORDER_CREATE_TIME,
O.ORDER_EFFECTIVE_TIME,
(SUBSTRING(O.ORDER_CREATE_TIME,1,4)-SUBSTRING(c.CUST_IDENTITY_CARD,7,4))-(SUBSTRING(c.CUST_IDENTITY_CARD,11,4)-DATE_FORMAT(O.ORDER_CREATE_TIME,’%m%d’)>0) as CUST_APPLICANT_AGE,
O.ORDER_DISTRICT AS CUST_HOME_ADDRESS,
O.ORDER_ISBLACK,
D.DICT_ITEM_NAME AS ORDER_REJECT_TYPE,
O.ORDER_DESC AS ORDER_REJECT_REASON,
F.DICT_ITEM_NAME AS SOURCE_TYPE
FROM
T_AGRIGUARANTEE_ORDER O
LEFT JOIN T_AGRIGUARANTEE_CUST C ON O.CUSTID = C.CUSTID
LEFT JOIN t_agriguarantee_bank B ON O.ORDER_BANK = B.BANK_CODE
LEFT JOIN t_agriguarantee_loanuse H ON O.ORDER_LOAN_PURPOSE = H.LOANUSEID
LEFT JOIN t_agriguarantee_dict D ON O.NO_PASS_TYPE = D.DICT_ID AND D.DICT_CODE = ‘NO_PASS_TYPE’
LEFT JOIN t_agriguarantee_dict F ON O.SOURCE_TYPE = F.DICT_ITEM_CODE AND F.DICT_CODE = ‘SOURCE_TYPE’
WHERE O.ORDER_ISBLACK IN (‘0’, ‘2’)

    order by O.ORDER_CREATE_TIME desc
</select>