mysql 常用查询语句

--创建split存储过程
CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

--用法
SELECT SPLIT_STR('a|bb|ccc|dd', '|', 3) as third;

--可以统计字段长度 groub by
select count(0),length(e21) from report_statistics.zhuanli_gzsw group by length(e21);
2       0
3       7
4       8
503     9
10      10
4       11
4       12
11635   13
603     14
73      15
1       16
1       23

--统计 数据截取及case when
insert overwrite local directory '/data/2/zly/tmpdata/zhuanli_20180402'
row format delimited
fields terminated by '|'
select * from  report_statistics.zhuanli_sqgb where substr(trim(E21),1,length(E21)-1)='200910225858' ;

insert overwrite local directory '/data/2/zly/tmpdata/zhuanli_20180402'
row format delimited
fields terminated by '|'
select * from  report_statistics.zhuanli_swgb where (case when  E21 ='' then substr(trim(EZL),3,length(E21)-3)  else  substr(trim(E21),1,length(E21)-1) end) ='200910225858' ;