请教Oracle依时间顺序分组 并行转列

[l3751202]

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 这个归到哪一组呢

[l3751202]

引用 3 楼 restbely 的回复:

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 date……

不好意思,大哥,我的sample data 太有规律了,实际上,后面的时间是完全没有规律的. 但是效果就是你这个例子的结果 . 我想要把按时间为顺序的步骤1-步骤3为一组,顺着下来,遇到步骤1就归到组的Id+1.

[yinan9]

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;

[BenChiM888]

弟弟，你要仔细研究一下3楼的sql，应该满足你的需求的 即使有偏差，改动也不大。

row_number() over(order by dateD)  --按照时间排序
regexp_substr(id, '\d+') --截取 步骤1 步骤2 步骤3 后面的 1,2,3 这三个数字

--两者相减，得到的结果 既可以达到你想要的分组目的
--仔细研究一下，本来想给你写一个，定睛一瞧，3楼已经给出了答案，呵呵。

另外，看明白之后记得这里也要给分哦。

引用 4 楼 l3751202 的回复:

引用 3 楼 restbely 的回复: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,'……

[l3751202]

引用 3 楼 restbely 的回复:

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 date……

对不起,是我没看清楚. 可是可是又有一个新的问题.我补充在楼下.

[l3751202]

引用 6 楼 BenChiM888 的回复:

弟弟，你要仔细研究一下3楼的sql，应该满足你的需求的 即使有偏差，改动也不大。 SQL code?123456row_number() over(order by dateD) --按照时间排序regexp_substr(id, '\d+') --截取 步骤1 步骤2 步骤3 后面的 1,2,3 这三个数字 --两者相减，得到的结果 既可以达到你想要的分组目的……

嘿,确实我没有看明白,焦点放在辣个时间上去了. 这个做法是通过减去步骤1,2,3 的数字,得到一个共同的数字去做分组的依据. 可是实际上的数据,步骤1,2,3这个字段的值是一些特定的值,但不会有这个规律,而且不一定每个分组出现的值都是一样的,例如: 第一组: 抽真空开始 抽真空结束 泄气开始 镀膜开始 镀膜结束 泄气结束 第二组 抽真空开始 抽真空结束 泄气开始 镀膜开始 镀膜结束 镀膜开始 镀膜结束 异常1 泄气结束 这样的数据可以套用吗?对不起,我sql比较烂... 另外,我不是弟弟,哇哈哈哈~

[wanglipo]

三楼的SQL很巧妙，值得一赞

[l3751202]

引用 1 楼 canhui87 的回复:

步骤3 11/17 00:00 这个归到哪一组呢

忘记说明,每一组从步骤1开始,步骤1前面的记录忽略. 如果不能忽略,则自己归一组囖.

[小海葵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 * 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 偷懒啦。

[BenChiM888]

这个好办，你建一个表 把你写的这些东西插进去，按照指定顺序编个号，这个表作为配置表 再利用3楼的sql 就ok了，问题不大。

引用 8 楼 l3751202 的回复:

引用 6 楼 BenChiM888 的回复:弟弟，你要仔细研究一下3楼的sql，应该满足你的需求的 即使有偏差，改动也不大。 SQL code?123456row_number() over(order by dateD) --按照时间排序regexp_substr(id, '\d+') --截取 步骤1 步骤2 步骤3 后面的 1,2,3 这三个数字 --两者……