id date
步骤3 11/17 00:00
步骤1 11/17 01:00
步骤2 11/17 02:00
步骤3 11/17 03:00
步骤1 11/17 04:00
步骤2 11/17 05:00
步骤3 11/17 06:00原始数据如以上所示,想要得到这样的结果:序号 步骤1 步骤2 步骤3
1 11/17 01:00 11/17 02:00 11/17 03:00
2 11/17 04:00 11/17 05:00 11/17 06:00如何通过sql取得这样的结果?
步骤3 11/17 00:00
步骤1 11/17 01:00
步骤2 11/17 02:00
步骤3 11/17 03:00
步骤1 11/17 04:00
步骤2 11/17 05:00
步骤3 11/17 06:00原始数据如以上所示,想要得到这样的结果:序号 步骤1 步骤2 步骤3
1 11/17 01:00 11/17 02:00 11/17 03:00
2 11/17 04:00 11/17 05:00 11/17 06:00如何通过sql取得这样的结果?
解决方案 »
- 求一个SQL语句
- 请教:如何跟踪delphi开发的程序的数据库操作
- 查看和修改数据库名(oracle,sql server,mysql)
- oracle10g的dmp能否导入到oracle9i的数据库?
- Oracle中修改访问访问权限,菜单名应该填什么名称
- oracle10G 监听器启动 链接时就死掉了 高手帮帮我啊
- 学了Oracle一个星期_一直困扰我的问题
- 用connect by实现树状结构时如何只显示最末端树叶的数据?
- 我的oracle建库脚本以前在oracle上运行没有问题,我在另外一个oracle上跑是却老是报错,但并不是所有的都错,请问怎么回事啊?
- oracle如何缩小表空间的大小
- Oracle SQL Developer 字符集问题,实在搜索不到解决方法
- ◆oracle9i 存储过程返回数据集的写法?◆
SELECT 'bz3' as id,'11/17 00:00' AS dateD from dual
union all
SELECT 'bz1' as id,'11/17 01:00' AS dateD from dual
union all
SELECT 'bz2' as id,'11/17 02:00' AS dateD from dual
union all
SELECT 'bz3' as id,'11/17 03:00' AS dateD from dual
union all
SELECT 'bz1' as id,'11/17 04:00' AS dateD from dual
union all
SELECT 'bz2' as id,'11/17 05:00' AS dateD from dual
union all
SELECT 'bz3' as id,'11/17 06:00' AS dateD from dual
union all
SELECT 'bz1' as id,'11/17 07:00' AS dateD from dual
)
select *
from (select max(decode(ID, 'bz1', dateD, '')) as str1,
max(decode(ID, 'bz2', dateD, '')) as str2,
max(decode(ID, 'bz3', dateD, '')) as str3
from (select row_number() over(order by dateD) - regexp_substr(id, '\d+') as rn,
ID,
dateD
from test)
group by rn)
where str1 is not null
and str2 is not null
and str2 is not null
--------------------------------------------
1 11/17 01:00 11/17 02:00 11/17 03:00
2 11/17 04:00 11/17 05:00 11/17 06:00偷懒啦。
不好意思,大哥,我的sample data 太有规律了,实际上,后面的时间是完全没有规律的.
但是效果就是你这个例子的结果 .我想要把按时间为顺序的步骤1-步骤3为一组,顺着下来,遇到步骤1就归到组的Id+1.
with test as
(SELECT 'bz3' as id, '11/17 00:00' AS dateD
from dual
union all
SELECT 'bz1' as id, '11/17 01:00' AS dateD
from dual
union all
SELECT 'bz2' as id, '11/17 02:00' AS dateD
from dual
union all
SELECT 'bz3' as id, '11/17 03:00' AS dateD
from dual
union all
SELECT 'bz1' as id, '11/17 04:00' AS dateD
from dual
union all
SELECT 'bz2' as id, '11/17 05:00' AS dateD
from dual
union all
SELECT 'bz3' as id, '11/17 06:00' AS dateD
from dual
union all
SELECT 'bz1' as id, '11/17 07:00' AS dateD from dual)
select rk,
max(decode(ID, 'bz1', dateD, '')) as str1,
max(decode(ID, 'bz2', dateD, '')) as str2,
max(decode(ID, 'bz3', dateD, '')) as str3
from (select id,
dateD,
row_number() over(partition by id order by dateD asc) rk
from test)
group by rk;
即使有偏差,改动也不大。
row_number() over(order by dateD) --按照时间排序
regexp_substr(id, '\d+') --截取 步骤1 步骤2 步骤3 后面的 1,2,3 这三个数字--两者相减,得到的结果 既可以达到你想要的分组目的
--仔细研究一下,本来想给你写一个,定睛一瞧,3楼已经给出了答案,呵呵。另外,看明白之后记得这里也要给分哦。
嘿,确实我没有看明白,焦点放在辣个时间上去了.
这个做法是通过减去步骤1,2,3 的数字,得到一个共同的数字去做分组的依据.
可是实际上的数据,步骤1,2,3这个字段的值是一些特定的值,但不会有这个规律,而且不一定每个分组出现的值都是一样的,例如:
第一组:
抽真空开始
抽真空结束
泄气开始
镀膜开始
镀膜结束
泄气结束
第二组
抽真空开始
抽真空结束
泄气开始
镀膜开始
镀膜结束
镀膜开始
镀膜结束
异常1
泄气结束这样的数据可以套用吗?对不起,我sql比较烂...
另外,我不是弟弟,哇哈哈哈~
把你写的这些东西插进去,按照指定顺序编个号,这个表作为配置表
再利用3楼的sql 就ok了,问题不大。