select t1.ip, count(ip) aanum, d15mi
from RPT_BAK_20120512 t1,
where t1.type = '2'
group by ip,d15mi
结果：
ip aanum d15mi
10.138.250.103 18 201205120015
10.136.16.197 8 201205120015
10.255.235.223 1 201205120130
10.179.185.209 3 201205120130
10.137.59.23 10 201205120215
10.137.69.234 7 201205120245
10.136.222.6 3 201205120245
10.136.15.154 2 201205120245
10.139.151.42 52 201205120330
10.136.19.105 2 201205120330
10.4.2.11 2 2 201205120345
10.96.30.36 1 201205120345
10.136.35.8 1 201205120345
10.196.130.136 1 201205120400
10.1236.135.18 4 201205120400
现在想要的结果列是
dday,ip ,aanum ,mi15,mi30,mi45,mi60
dday 为2012051204 这样到小时的时间字段
mi15 时间最后两位是 15,mi30 时间最后两位是 30,
mi45 时间最后两位是 45,mi60 时间最后两位是 00
对于同一个ip在同一个小时里面，mi15,mi30,mi45,mi60 都是有值的
求这sql如何写？
谢谢。
------解决方案--------------------
select substr(d15mi,1,10) dday,ip,count(ip) aanum,case substr(d15mi,10) when 15 then 15 end mi15,
case substr(d15mi,10) when 30 then 30 end mi30,
case substr(d15mi,10) when 45 then 45 end mi45,
case substr(d15mi,10) when 00 then 00 end mi65
from RPT_BAK_20120512
where type='2'
group by dday,ip,mi15,mi30,mi45,mi65;
------解决方案--------------------
- SQL code
SELECT SUBSTR(D15M, 1, 10) AS DDAY, IP, COUNT(1) AS AANUM, SUM(DECODE(SUBSTR(D15M, 11), 15, 1, 0)) MI15, SUM(DECODE(SUBSTR(D15M, 11), 30, 1, 0)) MI30, SUM(DECODE(SUBSTR(D15M, 11), 45, 1, 0)) MI45, SUM(DECODE(SUBSTR(D15M, 11), 00, 1, 0)) MI60 FROM A GROUP BY SUBSTR(D15M, 1, 10), IP;
------解决方案--------------------
这样？
- SQL code
with t1 as( select 2012051211 dtime,'11.140.18.122' ip,2 MI15,null MI30,null MI45,null MI60 from dual union all select 2012051211 dtime,'11.140.18.122' ip,null MI15,2 MI30,null MI45,null MI60 from dual union all select 2012051211 dtime,'11.140.18.122' ip,null MI15,null MI30,3 MI45,null MI60 from dual union all select 2012051211 dtime,'11.140.18.122' ip,null MI15,null MI30,null MI45,4 MI60 from dual)select dtime, ip, sum(nvl(MI15,0)) d15mi, sum(nvl(MI30,0)) d30mi, sum(nvl(MI45,0)) d45mi, sum(nvl(MI60,0)) d60mifrom t1group by dtime,ip DTIME IP MI15 MI30 MI45 MI60-----------------------------------1 2012051211 11.140.18.122 2 2 3 4