做了一段时间的性能测试,把自己在性能测试过程中,使用到的Oracle中用到的sql语句整理一番,做个备忘;
(1)多个字段以某种格式拼接
Oracle方式:"||"字符串拼接符;
示例:将“id”及“code”用逗号拼接: select t.id||','||t.code from OTable t;
MySQL方式:使用 concat()函数;
示例:select concat(t.id,',',t.code) from MTable t;
(2)以某一个字段为维度统计数量
Oracle方式:count + group by;
示例:统计某个创建人对应的数据数量:select t.creatorname,count(1) from OTable t (此处省去where条件查询) group by t.creatorname;
(3)统计每秒生成的数据的数量,并按数量由大到小进行排序-----------结果常用于性能测试目标TPS确定的参考值;
Oracle方式:count + group by + order by;(修改下面的sql中to_char的格式,可修改精确度为min或者hour)
示例:select to_char(t.Createtime,'yyyy-mm-dd hh24:mi:ss'),COUNT(1) from OTable t where
to_char(t.Createtime,'yyyy-mm-dd hh24:mi:ss') >='2016-01-01 00:00:00' and
to_char(t.Createtime,'yyyy-mm-dd hh24:mi:ss') <='2016-12-31 23:59:59'
group by to_char(t.Createtime,'yyyy-mm-dd hh24:mi:ss')
order by count(1) desc;
MySQL方式:DATE_FORMAT+ group by + order by;(修改DATE_FORMAT中的第二个参数,可修改精确度为min或者hour)
示例:select DATE_FORMAT(start_time,'%Y%m%d %H%i%s') iSecond,count(id) as icount
from MTable t where t.start_time BETWEEN '2017-06-01 00:00:00' and '2017-06-01 23:59:59'
group by iSecond
order by icount desc;
(4)统计某张表两个时间的时间差
MySQL方式:TIMESTAMPDIFF()函数,计算两个时间之间的差值,若第一个时间参数值晚于第二个时间参数值,则结果为负值;
示例:SELECT t.START_TIME, t.FINISH_TIME,
TIMESTAMPDIFF(SECOND, t.START_TIME, t.FINISH_TIME) AS `iSecond`
from MTable t where t.start_time BETWEEN '2017-05-10 00:00:00' and '2017-06-30 23:59:59';
大学贫困生申请书:https://www.nanss.com/xuexi/8066.html
师范生实习自我鉴定:https://www.nanss.com/xuexi/7965.html
班主任工作实习计划:https://www.nanss.com/gongzuo/9691.html
高校人才培养方案:https://www.nanss.com/gongzuo/8011.html
形容天气冷的句子:https://www.nanss.com/xuexi/9964.html
我的偶像作文:https://www.nanss.com/xuexi/9258.html
这就是幸福作文:https://www.nanss.com/xuexi/8806.html
仙气飘飘的四个字名字:https://www.nanss.com/mingcheng/8666.html
外出考察方案:https://www.nanss.com/gongzuo/9882.html
社区工作总结:https://www.nanss.com/gongzuo/8251.html
工作感想:https://www.nanss.com/gongzuo/9046.html
有关读书的名言:https://www.nanss.com/xuexi/7683.html
我得到了表扬作文:https://www.nanss.com/xuexi/9684.html
宝葫芦的秘密读后感:https://www.nanss.com/yuedu/9556.html
霸气网名大全:https://www.nanss.com/mingcheng/8647.html
最有诗意的网名:https://www.nanss.com/mingcheng/9905.html
形容爱情的成语:https://www.nanss.com/shenghuo/7742.html
因为有你作文:https://www.nanss.com/xuexi/8335.html
雾都孤儿读后感:https://www.nanss.com/yuedu/9052.html
篮球开头优美语段:https://www.nanss.com/xuexi/9132.html
那些不食人间烟火的古风名字:https://www.nanss.com/shenghuo/8155.html
非主流情侣网名:https://www.nanss.com/mingcheng/9589.html
捉蚊趣事作文:https://www.nanss.com/xuexi/8209.html
写景作文300字:https://www.nanss.com/xuexi/7767.html
分享作文:https://www.nanss.com/xuexi/8370.html
个人专业技术总结:https://www.nanss.com/gongzuo/9976.html
运动会班级口号:https://www.nanss.com/xuexi/9049.html
珍惜作文:https://www.nanss.com/xuexi/9863.html
万圣节活动策划:https://www.nanss.com/gongzuo/7907.html
个性网名女生:https://www.nanss.com/mingcheng/9300.html