目录

读取文件之后加回车

配合excel生成批量脚本

MYSql SUM计算数量

MYSql 新增字段

MYSql update 连表更新

由子及父–向上循环

由父及子–向下循环

读取文件之后加回车

if (null != stream) {InputStreamReader reader = new InputStreamReader(stream);BufferedReader buffReader = new BufferedReader(reader);String strTmp = "";while ((strTmp = buffReader.readLine()) != null) {stringBuffer.append(String.format("%s%n",strTmp));}}

配合excel生成批量脚本

=”(‘”&A1&”‘,'”&B1&”‘,'”&C1&”‘,'”&D1&”‘,'”&E1&”‘,'”&F1&”‘,'”&G1&”‘),”

=”insert into option (id,unit) values(“&&”,’”&&”’);”

MYSql SUM计算数量

selectSUM( IF( ins.coupons_rule_id IS NOT NULL , 1, 0 )) AS totalNum,SUM( IF( ins.use_status='USED', 1, 0 )) as usedNumfromdtma_activity_coupons_rule tinner join dtma_activity_coupons_info info on t.coupons_activity_id = info.coupons_activity_idLEFT JOIN dtma_activity_coupons_instationins ON ins.coupons_rule_id = t.coupons_rule_idwhere 1 = 1GROUP BY t.coupons_rule_id

MYSql 新增字段

CALL Pro_ColumnWork (‘ims’,’dt_distributor_info’,’fee’,1,”DECIMAL(24,6) NULL DEFAULT ‘0.000000’ COMMENT ‘费率'”);

MYSql update 连表更新

updatecmjl_ms_item_charge

leftjoincmjl_ms_itemoncmjl_ms_item.id=cmjl_ms_item_charge.cmjl_ms_item_id

setcmjl_ms_item_charge.uncertainty_accuracy_permission=cmjl_ms_item.uncertainty_accuracy_permission

wherecmjl_ms_item_charge.delete_ind=false;

由子及父–向上循环

<select id=”getMenuItemList” parameterType=”map” resultType=”java.util.HashMap” flushCache=”true”>
select t.id as url,t.cnName as label from (
select @id idlist,

(select @id:=group_concat(supId separator ‘,’) from sfxt_category where find_in_set(id,@id)) sub

from sfxt_category,(select @id:=#{id}) vars

where @id is not null) tl,sfxt_category t

where find_in_set(t.id,tl.idlist)
</select>

由父及子–向下循环

<select id=”getCatByDrug” resultType=”string”>
SELECT u2.id
FROM(
SELECT @ids AS supId,
(SELECT @ids := GROUP_CONCAT(id)
FROM sfxt_category
WHERE FIND_IN_SET(supId, @ids)) AS c_ids
FROM sfxt_category, (
SELECT @ids := 2000000, @l := 0) b ) u1
JOIN sfxt_category u2 ON FIND_IN_SET(u2.id, u1.supId) AND u2.id != 2000000
</select>