1、在开发中,我们常常需要把前台input传入的多个值的数组,拼接成一个字符串,中间用","隔开,再传入到后台做查询
String [] strs = {"A","B","C","D","E","F","G","H","I"};
StringBuilder sb = new StringBuilder();
for (int i = 0; i < strs.length; i++) {
sb.append("'"+strs[i]+"'");//拼接单引号,到数据库后台用in查询.
if(i!=strs.length-1){//前面的元素后面全拼上",",最后一个元素后不拼
sb.append(",");
}
}
2、在开发者有一次报表的统计要求统计三家银行的合计信息,但是有一家银行有特殊判断条件,这个sql该怎么写纠结了好半天,最后在百度下加上自己的修改终于搞定。
SELECT
COUNT (*) CUNT,
SUM (b.B_AMOUNT) + SUM (b.L_AMOUNT) SUM_TRAN_AMOUNT,
SUM (b.B_AMOUNT) BENJIN,
SUM (b.L_AMOUNT) LIXI
FROM VIEW_TRACE V, VIEW_TRACE O
LEFT JOIN VIEW_MERGE_TEMP B ON O.ACCOUNT_ID = B.ACCOUNT_ID
WHERE 1 = 1
AND (CASE WHEN v.BANK_CODE='XXX' THEN V.FRONT_TRACE ELSE '1' END)=(CASE WHEN v.BANK_CODE='XXX' THEN O.FRONT_TRACE ELSE '1' END)
AND substr(v.BANK_DATE,1,6)='201604'
AND V.TRAN_AMOUNT > 0
GROUP BY SUBSTR (v.BANK_DATE, 1, 6)
ORDER BY SUBSTR (v.BANK_DATE, 1, 6)
用case 。。。 when。。。语句
(CASE WHEN v.BANK_CODE='XXX' THEN V.FRONT_TRACE ELSE '1' END)=(CASE WHEN v.BANK_CODE='XXX' THEN O.FRONT_TRACE ELSE '1' END)
刚好可以筛选出这家银行的数据。