(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