MySQL各种行格式应用场景_mysql 常见使用场景

场景1：表中多条相同账户数据，根据创建日期排序取第一条。

select * from cms_client_rpq rpq

where id = (

select id from cms_client_rpq

where cif_no = rpq.cif_no

order by create_time desc

limit 1

)

场景2.primary key 不区分大小写

ALTER TABLE `cms_rpq_risk_report`

CHANGE COLUMN `accountid` `accountid` VARCHAR(50) binary primary key ;//设置为binary

场景3.将表A 中的字段col_a，更新到表B 的字段col_b,用表A的id，关联表B 的p_id

update A a,B b set b.col_b=a.col_a

where b.p_id=a.id

场景4.查找某个字符串中，某个字段出现的次数

length(accountids)-length(replace(accountids,'#','')) length

场景5.动态行转列

原表数据样式:

将不同时间段受教育时间转成列:

select

educate.user_id,

educate.user_name,

college.start_time college_start_time,

college.end_time college_end_time,

senior.start_time senior_start_time,

senior.end_time senior_end_time

from

(select distinct user_id,user_name from user_educate_base_info) educate

left join user_educate_base_info college on college.user_id=educate.user_id and college.education='college'

left join user_educate_base_info senior on senior.user_id=educate.user_id and senior.education='senior'