日期:2014-05-17  浏览次数:20695 次

求教一个sql写法
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)) d60mi
from t1
group by dtime,ip

      DTIME         IP           MI15     MI30   MI45    MI60
-----------------------------------
1    2012051211    11.140.18.122    2    2    3    4