开发测试中使用SQL的经验累积(不断更新)
在日常开发测试中,不断使用SQL,如MySql及Impala。记录下解决的问题。
MySql
- 字段连接
使用concat函数可以连接多个字符串
select concat(year,'-',month,'-',day) dateline from database.table;
- 字符型转整型
使用convert(string,signed)可以将string转换为整型
select opentityid dimension,max(convert(done,signed))*1000000 cost from Adx.OptLog where opentityid='15151_91bb6ebc0e85bb3d' and optype in (30001,20006) and optime between '2017-11-09 00:00:00' and '2017-11-09 23:59:59' and done >0 group by opentityid;
- update的条件带有子查询
将子查询外面包一层select即可将子查询的结果作为update的条件
update muggletest.testplan set current_tester=? where id=? and current_tester='' and (select t.tester from (select count(current_tester) tester from muggletest.testplan where current_tester=?) t)=0;
- 左填充
使用lpad(str,len,padstr),左填充用字符串padstr填补到len字符长度。
如果str为大于len长,返回值被缩短至len个字符。
ps:如果是1~9月份就会返回01~09啦
select concat(year,'-',lpad(month,2,'0'),'-',lpad(day,2,'0')) as ymd from database.table;
Impala
- 使用子查询中的字段必须给出别名
使用子查询中的字段没有别名impala的sql是不认的
select max(tmp.cc) from (select cid,count(*) cc from mid_blk where dateline='20171105' group by cid) tmp;
当然也可以用
select count(*) cc from mid_blk where dateline='20171105' group by cid order by cc desc limit 1;
只是举个例子
- 字符串右截取
使用strright(string,length)可以从字符串右侧开始截取最大为指定长度的字符
select concat(cast(year as string),'-',lpad(strright(cast(month as string),2),2,'0'),'-',lpad(cast(day as string),2,'0')) as ymd from database.table;