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如何写? 谢谢。
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如何写? 谢谢。
解决方案 »
- 两张表之间如何删除主键不同的记录
- oracle sql语句小问题
- csdn的大侠们,帮忙看看,在线等~
- ORACLE配置问题 急啊!!!!
- 关于动态SQL触发器的问题
- oracle如何进行数据迁移?就是用命令行将一台机器的库转移到另一台database server
- Oracle9.2.0.2安装时发生GetAllOracleHomes错误自动退出,报告jvm.dll的问题
- 一个导入导出问题,一个有点难度SQL语句问题,急!!
- RedHat Linux7.2 下安装oracle9i 出现问题
- 关于plsql,我第一次接触,很急,一点之前希望有人可以回答
- oracle database links 查询问题
- ProC编译选项sqlcheck=full加了之后报错,有没有人遇到过。(分可以再加的哦)
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;
DTIME IP MI15 MI30 MI45 MI60
2012051211 11.140.18.122 2
2012051211 11.140.18.122 2
2012051211 11.140.18.122 3
2012051211 11.140.18.122 4合并到一条记录上去。
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;
希望的结果是:
dtime ip mi15 mi30 mi45 mi60
2012051211 11.140.18.122 4 2 10 5意思是2012年05月12日11点 0~15内 分有4条,15~30分2条,30~45分10条,45~60分5条,
合并到一条上面。
2012062611,10.10.10.10,2,4,5,10就是这样的效果
就是每一个15分都有值。
之前发的没排好应该是
DTIME IP MI15 MI30 MI45 MI60
2012051211 11.140.18.122 2
2012051211 11.140.18.122 2
2012051211 11.140.18.122 3
2012051211 11.140.18.122 4合并到一条记录上去:DTIME IP MI15 MI30 MI45 MI60
2012051211 11.140.18.122 2 2 3 4这样的结果
DTIME,IP,MI15,MI30,MI45,MI60
2012051211,11.140.18.122,2
2012051211,11.140.18.122,,2
2012051211,11.140.18.122,,,3
2012051211,11.140.18.122,,,,4
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
然后用merge,却什么也没改变,百思不得其解。
不过现在这个sql已经得到需要的结果了。感谢
with t1 as
(
select 2012051211 dtime,'11.140.18.122' ip,1 MI15,null MI30,null MI45,null MI60 from dual
union all
select 2012051211 dtime,'11.140.18.122' ip,null MI15,5 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,d15mi,d30mi,d45mi,d60mi,
decode(sign(decode(sign(d15mi-d30mi),1,d15mi,d30mi)-decode(sign(d45mi-d60mi),1,d45mi,d60mi)),
1,decode(sign(d15mi-d30mi),1,d15mi,d30mi),decode(sign(d45mi-d60mi),1,d45mi,d60mi)) m_mi
from
(
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 d15mi d30mi d45mi d60mi m_di
-------------------------------------------------------------------------------
1 2012051211 11.140.18.122 1 5 3 4 5