mysql常用语句

创建表:
create table if not exists 表名 ( RECORDID varchar(36) not null comment '主键ID', TASKID varchar(36) comment '指标id',
primary key (RECORDID) ); alter table J表名 comment '注释';

添加字段存储过程:
call AddColumnUnlessExists('表名','字段名','数据类型','"注释"');

添加数据可重复执行:

insert into S_CODEITEMDEFINE(dmcod, id,  dmgrp,  dmcpt, dmlevcod, dmabr1, dmabr2, inpabr, dmhnd, porder, yesprv, attribute, iscanuse,  createtime, updatetime, deleteflag) select  '5','5','MBZDW','亿元','0','亿元','亿元','亿元','1','5','1','1','1','2020-12-13 09:47:49','2020-12-13 09:47:49','0' from dual where not exists(select * from s_codeitemdefine where id = '5' and DMGRP = 'MBZDW');

时间类型

to_char(字段名,'yyyy-MM-dd') as 别名        'yyyy-MM-dd HH:mm:ss'

TO_CHAR(now(),'yyyy')||'年'||TO_CHAR(now(),'mm')||'月'||TO_CHAR(now(),'dd')||'日' as time

添加/删除字段:

ALTER TABLE 表名 ADD 字段名 varchar(1000)

ALTER TABLE 表名 DROP COLUMN 字段名;

根据出生日期计算年龄

TRUNC( MONTHS_BETWEEN(SYSDATE, 字段)/12)

计算性别男的百分比

CONCAT(ROUND(ROUND((总数量 / (总数量 + 0.0)), 2) * 100, 0), '%')  AS man

oracle查单位父节点以及子节点

JOB.DEPARTMENTID IN(SELECT DEPARTMENTID FROM GKZP_DEPARTMENT START WITH DEPARTMENTID = #{DEPARTMENTID} CONNECT BY NOCYCLE PRIOR DEPARTMENTID = PARENTID)

mysql查单位父节点以及子节点

SELECT id, branch_name
FROM (SELECT t1.id,
             t1.branch_name,
             IF(find_in_set(parentid, @pids) > 0, @pids := concat(@pids, ',', id), 0) AS ischild
      FROM (SELECT id, branch_name, parentid FROM emanagement_branch t ORDER BY id) t1,
           (SELECT @pids := '2x') t2
      UNION
      SELECT id, branch_name, id AS ischild
      FROM emanagement_branch
      WHERE id = '2x') t3
WHERE ischild != '0'

相同id合并其他字段（达梦）:

select XMLUNITID,XMLUNITNAME,wm_concat(NAME||':'||IMPORTFAILUREREASON ) from PSKHBATCH_IMPORT_RECORD group by XMLUNITID,XMLUNITNAME;