MySQL工作常用方法

(1)mysql去重------distinct 和 group by

select
t.GN_ID,
t.ID,
t.DEPARTMENT_ID,
t.DEPARTMENT_NAME,
t.TYPE,
t.INSTRUCTION,
t.CREATE_TIME,
t.AUDIT_TIME,
t.STATE,
t.REGION_ID,
t.REGION_NAME
from t_data_adjust t 
left join nk_gw_flow_state n on t.GW_ID = n.GW_ID and n.state = '02' 
where t.status='1' and n.HANDLER_ID ='01106001002'   and  n.GW_TYPE='SJTZ'
group by GN_ID
order by t.CREATE_TIME desc

select
DISTINCT t.GN_ID,
t.ID,
t.DEPARTMENT_ID,
t.DEPARTMENT_NAME,
t.TYPE,
t.INSTRUCTION,
t.CREATE_TIME,
t.AUDIT_TIME,
t.STATE,
t.REGION_ID,
t.REGION_NAME
from t_data_adjust t 
left join nk_gw_flow_state n on t.GW_ID = n.GW_ID and n.state = '02' 
where t.status='1' and n.HANDLER_ID = @{user.id}   and  n.GW_TYPE='SJTZ'
order by t.CREATE_TIME desc

(2)mysql格式化浮点数   50.0转取50整

FORMAT(数据,0) AS age

0表示取得小数点后得位数，改为1，指的是取小数点后一味

(3)mysql创建视图+连接符 ||

select t.*,v.FKQQ from T_GZRW t 
left join view_gzrw_fkqq v on t.id=v.RWID
where t.status='1'
--and t.year='2023'
order by t.CREATE_TIME desc;


create or replace view view_gzrw_fkqq as
select t.RWID,
SUM(case when t.STATE='1' then 1 else 0 end)||'/'||count(1) as fkqq
from T_HLXJC_STUDENT t where t.status='1'
group by t.RWID
order by t.RWID