程序人生 随笔记录= 小伎俩

目录

读取文件之后加回车

配合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计算数量

select

SUM( IF( ins.coupons_rule_id IS NOT NULL , 1, 0 )) AS totalNum,

SUM( IF( ins.use_status= 'USED', 1, 0 )) as usedNum

from dtma_activity_coupons_rule t

inner join dtma_activity_coupons_info info on t.coupons_activity_id = info.coupons_activity_id

LEFT JOIN dtma_activity_coupons_instation ins ON ins.coupons_rule_id = t.coupons_rule_id

where 1 = 1

GROUP 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 连表更新

update cmjl_ms_item_charge

left join cmjl_ms_item on cmjl_ms_item.id = cmjl_ms_item_charge.cmjl_ms_item_id

set cmjl_ms_item_charge.uncertainty_accuracy_permission = cmjl_ms_item.uncertainty_accuracy_permission

where cmjl_ms_item_charge.delete_ind = false;

由子及父--向上循环

    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)) subfrom sfxt_category,(select @id:=#{id}) varswhere @id is not null) tl,sfxt_category twhere find_in_set(t.id,tl.idlist)   

由父及子--向下循环

 SELECT u2.idFROM(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  

参考阅读

 您阅读本篇文章共花了：