问题描述
select t1.ip, count(ip) aanum, d15mi from RPT_BAK_20120512 t1, where t1.type = '2' group by ip,d15mi结果：ip aanum d15mi10.138.250.103 18 20120512001510.136.16.197 8 20120512001510.255.235.223 1 20120512013010.179.185.209 3 20120512013010.137.59.23 10 20120512021510.137.69.234 7 20120512024510.136.222.6 3 20120512024510.136.15.154 2 20120512024510.139.151.42 52 20120512033010.136.19.105 2 20120512033010.4.2.112 2 20120512034510.96.30.36 1 20120512034510.136.35.8 1 20120512034510.196.130.136 1 20120512040010.1236.135.18 4 201205120400现在想要的结果列是dday,ip ,aanum ,mi15,mi30,mi45,mi60dday 为2012051204 这样到小时的时间字段mi15 时间最后两位是 15,mi30 时间最后两位是 30,mi45 时间最后两位是 45,mi60 时间最后两位是 00对于同一个ip在同一个小时里面，mi15,mi30,mi45,mi60 都是有值的求这sql如何写？谢谢。 问题补充：就是把DTIME IP MI15 MI30 MI45 MI602012051211 11.140.18.122 2 2012051211 11.140.18.122 122012051211 11.140.18.122 32012051211 11.140.18.122 4合并到一条记录上去。
解决方案
select substr(t1.d15mi, 0, 10) dtime, ip, sum(case when substr(t1.d15mi, -2) = '15' then 1 end) mi15, sum(case when substr(t1.d15mi, -2) = '30' then 1 end) mi30, sum(case when substr(t1.d15mi, -2) = '45' then 1 end) mi45, sum(case when substr(t1.d15mi, -2) = '00' then 1 end) mi60 from RPT_BAK_20120512 t1 group by ip, substr(t1.d15mi, 0, 10);
解决方案二：
-- sql 很强大，你这个既不用procedure，也不用function即可完成-- 你是要行转列 你的需求建议用sum 和 decode 来完成，以下是个参考select ip, substr(t1.d15mi, 0, 10) dtime, sum(case when substr(t1.d15mi, 11, 2) = '15' then 1 end) mi15, sum(case when substr(t1.d15mi, 11, 2) = '30' then 1 end) mi15, sum(case when substr(t1.d15mi, 11, 2) = '45' then 1 end) mi15, sum(case when substr(t1.d15mi, 11, 2) = '60' then 1 end) mi15 from RPT_BAK_20120512 t1 group by ip, substr(t1.d15mi, 0, 10);
解决方案三：
用我上面的例子，在进行联合查询m15 是一个表 a，m30是一个表 ba.ip=b.ipSELECT t15.m15_ip,t15.mi15,t30.mi30 FROM (SELECT m15_ip, count(create_date) AS mi15 FROM t_test WHERE create_date REGEXP "15$" ) t15, (SELECT m30_ip,count(create_date) AS mi30 FROM t_test WHERE create_date REGEXP "30$" ) t30 where t15.m15_ip = t30.m30_ip时间也是类似，大概就是这么写法，这个语句我没有测试，可能有些细节问题，你自己需修改下 ， t15.m15_ip 这个也可以换成t30 的，因为去相同的部分，所以无所谓用那个，当然语句你可以换成inner join
解决方案四：
只能分别统计出来再拼接到一起了：select substr(t.d15min, 0, 10) dtime, t.ip, (select count(1) from RPT_BAK_20120512 mi15 where mi15.ip = t.ip and mi15.d15min = t.d15min and substr(mi15.d15min, 11, 2) = '15') MI15, (select count(1) from RPT_BAK_20120512 mi30 where mi30.ip = t.ip and mi30.d15min = t.d15min and substr(mi30.d15min, 11, 2) = '30') MI30, (select count(1) from RPT_BAK_20120512 mi45 where mi45.ip = t.ip and mi45.d15min = t.d15min and substr(mi45.d15min, 11, 2) = '45') MI45, (select count(1) from RPT_BAK_20120512 mi60 where mi60.ip = t.ip and mi60.d15min = t.d15min and substr(mi60.d15min, 11, 2) = '60') MI60 from RPT_BAK_20120512 t用你上面的数据执行的结果： DTIMEIPMI15MI30MI45MI601201205120110.255.235.22301002201205120110.179.185.20901003201205120210.137.59.2310004201205120210.137.69.234 00105201205120210.136.222.600106201205120210.136.15.15400107201205120310.139.151.4201008201205120310.136.19.10501009201205120310.4.2.11 001010201205120310.96.30.36001011201205120310.136.35.8001012201205120410.196.130.136000013201205120410.1236.135.18000014201205120010.138.250.103100015201205120010.136.16.1971000
解决方案五：
MYSQL 简单的例子ORACLE 不知道支不支持正则SELECT t15.mi15,t30.mi30 FROM (SELECT create_date AS mi15 FROM t_test WHERE create_date REGEXP "15$" ) t15,(SELECT create_date AS mi30 FROM t_test WHERE create_date REGEXP "30$" ) t30查询结果如下+--------------+--------------+| mi15 | mi30 |+--------------+--------------+| 201205120015 | 201205120130 || 201205120015 | 201205120130 || 201205120215 | 201205120130 || 201205120015 | 201205120330 || 201205120015 | 201205120330 || 201205120215 | 201205120330 || 201205120015 | 201205120330 || 201205120015 | 201205120330 || 201205120215 | 201205120330 |+--------------+--------------+
解决方案六：
用sql取不出来的。。。你可以对现在已有的数据用JAVA或者c代码进行分类啊，根据201205120015字符串(或者是Date)，分成2012051200和15，放到MAP里,Map<dday,Map<mi,value>>