实用的SQL语句

1.如果t1表id字段等于t2表id字段，则令t1表的center字段等于t2表的center字段

update t1  set center=(select center from t2  where t1.id=t2.id )
where t1.id in (select id from t2);

2.删除表中有问题的记录：

本来正确记录的字段值应该是1,2;33,22;11,222;可是有些记录的字段值却是1,2;5;33,22; 要把有;5;的记录删除了

(1) 先查出有问题的记录

--正常来讲,号的个数是和;个数相同，异常的记录二者不相同
select id,length(points)-length(replace(points,';','')),
length(points)-length(replace(points,',','')) 
from table1 where  length(points)-length(replace(points,',',''))!=
length(points)-length(replace(points,';',''));

(2) 删除有问题的记录

delete from table1
where length(points)-length(replace(points,',',''))!=
length(points)-length(replace(points,';',''));

3.行转列

--多行变多列
create table tmp(
name varchar(12),
nums int,
types varchar(12)
)engine=innodb default charset=utf8;

insert into tmp
values('小夫',22,'all');
insert into tmp
values('小夫',10,'success');
insert into tmp
values('小夫',6,'error');
insert into tmp
values('小夫',6,'timeout');

select name,
sum(case types when 'all' then nums else 0 end) as '全部',
sum(case types when 'success' then nums else 0 end ) as '成功',
sum(case types when 'error' then nums else 0 end ) as '错误',
sum(case types when 'timeout' then nums else 0 end ) as '超时'
from tmp;

--多行变一列
name   score
a      1
a      2
a      3
select name group_concat(score Separtor ',') as score from stu group by name;
--结果   a  1,2,3

4.用exists替换in

--in
select * from tab1 where sid not in (select sid from tab2);
--exists
select * from tab1 a where not exists (select 1 from tab2 where a.sid=b.sid);

5.mysql5.7分组后取创建时间最新的数据

表ecp_order_info_test内容
order_code ac_id create_time
a 1 20220329
b 1 20220328
d 2 20220419
f 2 20220410

执行sql:
错误的 select * from (select * from ecp_order_info_test order by create_time desc) t group by ac_id;
正确的 
select t.* 
from ecp_order_info_test t 
join (
select substring_index(group_concat(order_code order by create_time desc),',',1) as order_code from ecp_order_info_test group by ac_id) tmp
on t.order_code=tmp.order_code;

错误的sql在mysql5.5执行是正确的,而mysql5.7执行得到不需要的结果，因为5.7使用了新的优化器，使得查询语句的顺序做了调整直接没进行子查询

6.case when 对同一个字段进行多次匹配

--如果receive_time=2023051100 返回00 ，receive_time=2023051101 返回01，其他情况返回error 
select 
case when receive_time=2023051100 then 00
     when receive_time=2023051101 then 01 else 'error' end hour
from tab_a;