MySQL 日期格式化

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')

MySQL按日期分组统计

select date_format(create_time, '%Y-%m') mont, count(*) coun
from t_content
group by date_format(create_time, '%Y-%m');

查询MySQL SQL语句的进程

show processlist;

字符串去除前后空格

TRIM(srcStr)

case when

--
case 
  when 条件 then 结果
  when 条件 then 结果
  when 条件 then 结果
  else
end

-- 示例
case
     when TRIM(oo.owner_dept_no) = '716186.509317.595739.817263' then '产品部'
     when TRIM(oo.owner_dept_no) = '716186.509317.595739.817263.817275' then '用户体验设计部'
     when TRIM(oo.owner_dept_no) = '716186.509317.595739.817263.817277' then '营销产品部'
     when TRIM(oo.owner_dept_no) = '716186.509317.595739.817263.817278' then '智能办公产品部'
     when TRIM(oo.owner_dept_no) = '716186.509317.595739.817263.817279' then '综合物流产品部'
     when TRIM(oo.owner_dept_no) = '716186.509317.595739.817263.817280' then '财金产品部'
     when TRIM(oo.owner_dept_no) = '716186.509317.595739.817263.817282' then '营运产品部'
     when TRIM(oo.owner_dept_no) = '716186.509317.595739.817263.817283' then '产品运营部'
     when TRIM(oo.owner_dept_no) = '716186.509317.595739.817263.817282.953671' then '营运产品部'
     when TRIM(oo.owner_dept_no) = '716186.509317.595739.817263.817282.953672' then '营运产品部'
     when TRIM(oo.owner_dept_no) = '716186.509317.595739.817263.817282.953673' then '营运产品部'
   end
   as 三级部门

MySQL定义变量和使用变量

定义变量，一般而言变量名前面加@符号

• 定义全局变量：set global 变量名=变量值
• 定义局部变量：set 变量名=变量值

  set @periodStart = '2022-02-01';
  set @periodEnd = '2022-05-01';
  select count(distinct emp_no) from dim_usr_user 
  where entry_time < @periodEnd and leave_time >= @periodStart ;