mysql mybatis 函数_mybatis中常用方式或者mysql中得搜集

id, rwmc, rwms, rwzqks, rwzqjs, rwpc, xjry, sfcl, cjr, cjsj, xgr, xgsj, wccs,

remark2, remark3,pccs,sfwc, delete_status, szgw, xjscqy, cs

select DISTINCT tn.*

fromwp_dzxjrw tn-- join base_data_table bdt on bdt.tableid=tn.idwhere 1=1

and tn.${item.key} like CONCAT("%",#{item.value},"%")

or tn.${item.COLUMN_NAME} like CONCAT("%",#{mes},"%")

AND judgeDataPermission(tn.id,#{userId},tn.cjr)=1

order by tn.cjsj desc

select

fromwp_dzxjrwwhere id = #{id,jdbcType=VARCHAR}

deletefromwp_dzxjrwwhere id = #{id,jdbcType=VARCHAR}

insert into wp_dzxjrw

id,

rwmc,

rwms,

rwzqks,

rwzqjs,

rwpc,

xjry,

sfcl,

cjr,

cjsj,

xgr,

xgsj,

pccs,

sfwc,

wccs,

remark2,

remark3,

delete_status,

szgw,

xjscqy,

cs,

#{id,jdbcType=VARCHAR},

#{rwmc,jdbcType=VARCHAR},

#{rwms,jdbcType=VARCHAR},

#{rwzqks,jdbcType=TIMESTAMP},

#{rwzqjs,jdbcType=TIMESTAMP},

#{rwpc,jdbcType=VARCHAR},

#{xjry,jdbcType=VARCHAR},

#{sfcl,jdbcType=INTEGER},

#{cjr,jdbcType=VARCHAR},

#{cjsj,jdbcType=TIMESTAMP},

#{xgr,jdbcType=VARCHAR},

#{xgsj,jdbcType=TIMESTAMP},

#{pccs,jdbcType=INTEGER},

#{sfwc,jdbcType=INTEGER},

#{wccs,jdbcType=INTEGER},

#{remark2,jdbcType=VARCHAR},

#{remark3,jdbcType=VARCHAR},

#{deleteStatus,jdbcType=INTEGER},

#{szgw,jdbcType=VARCHAR},

#{xjscqy,jdbcType=VARCHAR},

#{cs,jdbcType=INTEGER},

update wp_dzxjrw

rwmc= #{rwmc,jdbcType=VARCHAR},

rwms= #{rwms,jdbcType=VARCHAR},

rwzqks= #{rwzqks,jdbcType=TIMESTAMP},

rwzqjs= #{rwzqjs,jdbcType=TIMESTAMP},

rwpc= #{rwpc,jdbcType=VARCHAR},

xjry= #{xjry,jdbcType=VARCHAR},

sfcl= #{sfcl,jdbcType=INTEGER},

cjr= #{cjr,jdbcType=VARCHAR},

cjsj= #{cjsj,jdbcType=TIMESTAMP},

xgr= #{xgr,jdbcType=VARCHAR},

xgsj= #{xgsj,jdbcType=TIMESTAMP},

pccs= #{pccs,jdbcType=INTEGER},

sfwc= #{sfwc,jdbcType=INTEGER},

wccs= #{wccs,jdbcType=INTEGER},

remark2= #{remark2,jdbcType=VARCHAR},

remark3= #{remark3,jdbcType=VARCHAR},

delete_status= #{deleteStatus,jdbcType=INTEGER},

szgw= #{szgw,jdbcType=VARCHAR},

xjscqy= #{xjscqy,jdbcType=VARCHAR},

cs= #{cs,jdbcType=INTEGER},

where id = #{id,jdbcType=VARCHAR}

select COLUMN_NAME frominformation_schema.COLUMNSwhere table_name = #{tableName} and table_schema = #{dbName} and COLUMN_NAME!="id" and COLUMN_NAME!="rwzqks" and COLUMN_NAME!="rwzqjs"and COLUMN_NAME!="cjsj" and COLUMN_NAME!="xgsj" and COLUMN_NAME!="pccs"and COLUMN_NAME!="sfwc"

selectDISTINCT d.id, d.rwmc, d.rwms, d.rwzqks, d.rwzqjs, d.rwpc, d.xjry,u.`name` xjryName,d.pccs,d.sfwc,d.cjsj, d.szgw, d.xjscqy,d.sfcl,d.csfromwp_dzxjrw d-- join base_data_table bdt on bdt.tableid=d.id

LEFT JOIN base_user u on d.xjry=u.idwhere 1=1

AND d.szgw LIKE CONCAT(CONCAT('%',#{szgw,jdbcType=VARCHAR},'%'))

AND d.xjscqy LIKE CONCAT(CONCAT('%',#{xjscqy,jdbcType=VARCHAR},'%'))

AND d.rwmc LIKE CONCAT(CONCAT('%',#{rwmc,jdbcType=VARCHAR},'%'))

AND d.rwms LIKE CONCAT(CONCAT('%',#{rwms,jdbcType=VARCHAR},'%'))

AND d.rwzqks>= #{rwzqks,jdbcType=TIMESTAMP} AND rwzqjs <= #{rwzqjs,jdbcType=TIMESTAMP}

AND d.rwpc LIKE CONCAT(CONCAT('%',#{rwpc,jdbcType=VARCHAR},'%'))

AND d.cs LIKE CONCAT(CONCAT('%',#{cs,jdbcType=VARCHAR},'%'))

AND u.`name` LIKE CONCAT(CONCAT('%',#{xjryName,jdbcType=VARCHAR},'%'))

AND CONCAT(d.rwmc, d.rwms, d.rwpc, u.`name`, d.szgw, d.xjscqy) LIKE CONCAT(CONCAT('%',#{all},'%'))

AND d.delete_status= 0AND judgeDataPermission(d.id,#{cjr},d.cjr)=1order by d.cjsj desc

selectd.id, d.rwmc, d.rwms, d.rwzqks, d.rwzqjs, d.rwpc, d.xjry,u.`name` xjryName,d.pccs,d.sfwc,d.cjsj, d.szgw, d.xjscqy,d.csfrom wp_dzxjrw d LEFT JOIN base_user u on d.xjry =u.idwhere d.id = #{id,jdbcType=VARCHAR}

select

fromwp_dzxjrwwhere 1=1 AND delete_status = 0 and DATE_FORMAT(rwzqks,"%Y-%m-%d")<=DATE_FORMAT(NOW(),"%Y-%m-%d")AND xjry= #{xjry,jdbcType=VARCHAR}

AND sfwc= 0

AND wccs!= 0

select *

fromwp_dzxjrwwhere 1=1 AND delete_status = 0 and sfwc = 0 and DATE_FORMAT(rwzqjs,"%Y-%m-%d")>=DATE_FORMAT(NOW(),"%Y-%m-%d")