查询一张表中的数据，多行并成一行显示。

[DamonYo0o]

现在有一张表
ID TIME TYPE
1001 2017-01-01 12:12:12 1
1001 2017-01-01 19:19:19 2
1002 2017-01-01 10:10:10 1
1002 2017-01-01 20:20:20 2
1003 2017-01-01 13:13:13 1
1003 2017-01-01 16:16:16 2

现在想做出这种效果
ID TIME_IN TYPE_IN TIME_OUT TYPE_OUT
1001 2017-01-01 12:12:12 1 2017-01-01 19:19:19 2
1002 2017-01-01 10:10:10 1 2017-01-01 20:20:20 2
1003 2017-01-01 13:13:13 1 2017-01-01 16:16:16 2

这个张表里每天都有很多数据，需要按照ID分组，然后再按照日期来区分时间段，查出来每个ID的每一天的最大时间和最小时间，然后合并成一行。

求大神帮忙写个ORACLE的SQL语句。

[DamonYo0o]

引用 8 楼 qq646748739 的回复:

[quote=引用 7 楼 weixin_38749068 的回复:] [quote=引用 6 楼 qq646748739 的回复:] 可以实现：

select id,
       max(decode(rn,1,time,null))  time_in, 
       max(decode(rn,1,type,null))  type_in,
       max(decode(rn1,1,time,null)) time_out,
       max(decode(rn1,1,type,null)) type_out
  from (select id, time,type,
               row_number()over(partition by id,trunc(time) order by time)  rn,
               row_number()over(partition by id,trunc(time)  order by time desc)  rn1
          from tmp
       )
 group by id;

我加了一个条件，type只能是1或者2，而且1表示的type_in和time_in ,2表示time_out 和type_out，可是不明白在哪做判断[/quote] type数据也只显示了1和2，现在你要的结果是什么？[/quote] type_out和time_out的数据有一条是type=1的，就是我标红的那一条，这里需要的是全部都是type=2的数据。。。 我想加个判断，不明白在哪里加上。。。

[碧水幽幽泉]

引用 9 楼 weixin_38749068 的回复:

[quote=引用 8 楼 qq646748739 的回复:]
[quote=引用 7 楼 weixin_38749068 的回复:]
[quote=引用 6 楼 qq646748739 的回复:]
可以实现：

select id,
       max(decode(rn,1,time,null))  time_in, 
       max(decode(rn,1,type,null))  type_in,
       max(decode(rn1,1,time,null)) time_out,
       max(decode(rn1,1,type,null)) type_out
  from (select id, time,type,
               row_number()over(partition by id,trunc(time) order by time)  rn,
               row_number()over(partition by id,trunc(time)  order by time desc)  rn1
          from tmp
       )
 group by id;

我加了一个条件，type只能是1或者2，而且1表示的type_in和time_in ,2表示time_out 和type_out，可是不明白在哪做判断[/quote]
type数据也只显示了1和2，现在你要的结果是什么？[/quote]

type_out和time_out的数据有一条是type=1的，就是我标红的那一条，这里需要的是全部都是type=2的数据。。。
我想加个判断，不明白在哪里加上。。。[/quote]
同一用户id下，你想要type=1 create_time最小的记录，type =2 create_time最大的记录？

[碧水幽幽泉]

--1.创建测试表
create table tmp as
select '1001' id, to_date('2017-01-01 12:12:12 ','yyyy-mm-dd hh24:mi:ss') time,  1 type from dual union all
select '1001' id, to_date('2017-01-01 16:16:16 ','yyyy-mm-dd hh24:mi:ss') time,  1 type from dual union all
select '1001' id, to_date('2017-01-01 11:11:11 ','yyyy-mm-dd hh24:mi:ss') time,  2 type from dual union all
select '1001' id, to_date('2017-01-01 19:19:19 ','yyyy-mm-dd hh24:mi:ss') time,  2 type from dual union all
select '1001' id, to_date('2017-01-01 20:20:20 ','yyyy-mm-dd hh24:mi:ss') time,  2 type from dual union all
select '1002' id, to_date('2017-01-01 10:10:10 ','yyyy-mm-dd hh24:mi:ss') time,  1 type from dual union all
select '1002' id, to_date('2017-01-01 20:20:20 ','yyyy-mm-dd hh24:mi:ss') time,  2 type from dual union all
select '1003' id, to_date('2017-01-01 13:13:13 ','yyyy-mm-dd hh24:mi:ss') time,  1 type from dual union all
select '1003' id, to_date('2017-01-01 16:16:16 ','yyyy-mm-dd hh24:mi:ss') time,  2 type from dual

--2.SQL实现: 同一用户id下，取type=1  create_time最小的记录，type =2 create_time最大的记录
select id,
       max(decode(rn,1,time,null))  time_in, 
       max(decode(rn,1,type,null))  type_in,
       max(decode(rn1,1,time,null)) time_out,
       max(decode(rn1,1,type,null)) type_out
  from (select id, time,type,
                case when type = 1 then row_number()over(partition by id,type,trunc(time) order by time) else null end rn,
                case when type = 2 then row_number()over(partition by id,type,trunc(time) order by time desc) else null end rn1
          from tmp
	     where type in (1,2)
       )
 group by id;

[DamonYo0o]

引用 11 楼 qq646748739 的回复:

--1.创建测试表
create table tmp as
select '1001' id, to_date('2017-01-01 12:12:12 ','yyyy-mm-dd hh24:mi:ss') time,  1 type from dual union all
select '1001' id, to_date('2017-01-01 16:16:16 ','yyyy-mm-dd hh24:mi:ss') time,  1 type from dual union all
select '1001' id, to_date('2017-01-01 11:11:11 ','yyyy-mm-dd hh24:mi:ss') time,  2 type from dual union all
select '1001' id, to_date('2017-01-01 19:19:19 ','yyyy-mm-dd hh24:mi:ss') time,  2 type from dual union all
select '1001' id, to_date('2017-01-01 20:20:20 ','yyyy-mm-dd hh24:mi:ss') time,  2 type from dual union all
select '1002' id, to_date('2017-01-01 10:10:10 ','yyyy-mm-dd hh24:mi:ss') time,  1 type from dual union all
select '1002' id, to_date('2017-01-01 20:20:20 ','yyyy-mm-dd hh24:mi:ss') time,  2 type from dual union all
select '1003' id, to_date('2017-01-01 13:13:13 ','yyyy-mm-dd hh24:mi:ss') time,  1 type from dual union all
select '1003' id, to_date('2017-01-01 16:16:16 ','yyyy-mm-dd hh24:mi:ss') time,  2 type from dual

--2.SQL实现: 同一用户id下，取type=1  create_time最小的记录，type =2 create_time最大的记录
select id,
       max(decode(rn,1,time,null))  time_in, 
       max(decode(rn,1,type,null))  type_in,
       max(decode(rn1,1,time,null)) time_out,
       max(decode(rn1,1,type,null)) type_out
  from (select id, time,type,
                case when type = 1 then row_number()over(partition by id,type,trunc(time) order by time) else null end rn,
                case when type = 2 then row_number()over(partition by id,type,trunc(time) order by time desc) else null end rn1
          from tmp
	     where type in (1,2)
       )
 group by id;

万分感谢！！！！

[碧水幽幽泉]

这个简单，典型的行转列！

[自由自在_Yu]

select id,min(TIME) TIME_IN ,1 TYPE_IN,max(TIME) TIME_OUT ,2 TYPE_OUT from table group by id

[碧水幽幽泉]

--1.创建测试表
create table tmp as
select '1001' id, to_date('2017-01-01 12:12:12 ','yyyy-mm-dd hh24:mi:ss') time,  1 type from dual union all
select '1001' id, to_date('2017-01-01 19:19:19 ','yyyy-mm-dd hh24:mi:ss') time,  2 type from dual union all
select '1002' id, to_date('2017-01-01 10:10:10 ','yyyy-mm-dd hh24:mi:ss') time,  1 type from dual union all
select '1002' id, to_date('2017-01-01 20:20:20 ','yyyy-mm-dd hh24:mi:ss') time,  2 type from dual union all
select '1003' id, to_date('2017-01-01 13:13:13 ','yyyy-mm-dd hh24:mi:ss') time,  1 type from dual union all
select '1003' id, to_date('2017-01-01 16:16:16 ','yyyy-mm-dd hh24:mi:ss') time,  2 type from dual

--2.SQL实现
select id,
       max(decode(rn,1,time,null))  time_in, 
       max(decode(rn,1,type,null))  type_in,
       max(decode(rn1,1,time,null)) type_out,
       max(decode(rn1,1,type,null)) type_out
  from (select id, time,type,
               row_number()over(partition by id order by time)  rn,
               row_number()over(partition by id order by time desc)  rn1
          from tmp
       )
 group by id;

[碧水幽幽泉]

max(decode(rn1,1,time,null)) type_out, 改成：max(decode(rn1,1,time,null)) time_out,
刚才copy错了。

[DamonYo0o]

引用 4 楼 qq646748739 的回复:

max(decode(rn1,1,time,null)) type_out, 改成：max(decode(rn1,1,time,null)) time_out,
刚才copy错了。

有些不太一样


最后需要查出来的不只是六条，前面说错了，要按时间来分组，
表里每个用户每天都有记录，而且记录不止一条，需要查出每个用户，每一天的最大时间和最小时间，快不认识这个每字了。。。
这个能实现吗

[碧水幽幽泉]

可以实现：

select id,
       max(decode(rn,1,time,null))  time_in, 
       max(decode(rn,1,type,null))  type_in,
       max(decode(rn1,1,time,null)) time_out,
       max(decode(rn1,1,type,null)) type_out
  from (select id, time,type,
               row_number()over(partition by id,trunc(time) order by time)  rn,
               row_number()over(partition by id,trunc(time)  order by time desc)  rn1
          from tmp
       )
 group by id;

[DamonYo0o]

引用 6 楼 qq646748739 的回复:

可以实现：

select id,
       max(decode(rn,1,time,null))  time_in, 
       max(decode(rn,1,type,null))  type_in,
       max(decode(rn1,1,time,null)) time_out,
       max(decode(rn1,1,type,null)) type_out
  from (select id, time,type,
               row_number()over(partition by id,trunc(time) order by time)  rn,
               row_number()over(partition by id,trunc(time)  order by time desc)  rn1
          from tmp
       )
 group by id;

我加了一个条件，type只能是1或者2，而且1表示的type_in和time_in ,2表示time_out 和type_out，可是不明白在哪做判断

[碧水幽幽泉]

引用 7 楼 weixin_38749068 的回复:

[quote=引用 6 楼 qq646748739 的回复:]
可以实现：

select id,
       max(decode(rn,1,time,null))  time_in, 
       max(decode(rn,1,type,null))  type_in,
       max(decode(rn1,1,time,null)) time_out,
       max(decode(rn1,1,type,null)) type_out
  from (select id, time,type,
               row_number()over(partition by id,trunc(time) order by time)  rn,
               row_number()over(partition by id,trunc(time)  order by time desc)  rn1
          from tmp
       )
 group by id;

我加了一个条件，type只能是1或者2，而且1表示的type_in和time_in ,2表示time_out 和type_out，可是不明白在哪做判断[/quote]
type数据也只显示了1和2，现在你要的结果是什么？