mysql语句记载

1、对单个字段去重
select * from 表名 where 主键 in(select max(主键) from 表名 group by 要去重的字段 )
例：
SELECT openid,consultant_num,shareopenid,shareorscan,unionid,shareunionid,bind_time,FROM_UNIXTIME(bind_time)
FROM prm2020.wp_bind_consultant where id in(select max(id)
from prm2020.wp_bind_consultant where openid is not null and unionid is not null
and FROM_UNIXTIME(bind_time)>=‘2018-09-01 00:00:00’
group by unionid )
2、查询重复出现的数据有多少
例：
select * from prm2020.wp_agency_earnings group by cons_num,goods_code,order_code,e_type having count(*) > 1
3、不同库之间对比相同修改数据
例：
UPDATE platform_vshop_db.m_temp_bind_rel_consultant c,
prm2020.wp_bind_consultant b
set c.create_time = FROM_UNIXTIME(b.bind_time)
where b.unionid = c.union_id
4、两表联合查询修改其中单表数据
例：
update platform_vshop_db.c_agent_picked_address as z left join platform_vshop_db.c_bank_region_area as zb
on z.provice_code = zb.region_name
set z.provice_code = zb.region_code
where zb.region_type=1;
5、两表联合查询修改其中单表数据（去空格且只要前两位进行对比）（多用于市name换成code，省市名称对比）
例：
update platform_vshop_db.c_consultant_bank_info as z left join
platform_vshop_db.c_bank_region_area as zb on left(replace(z.bank_city_code,’ ‘,’’),2) = left(zb.region_name ,2)
set z.bank_city_code = zb.region_code where zb.region_type=3;