select c.id,c.statu1,d.statu2 from
(select a.id,a.statu1 from jl a,(select id,min(time) time0 from jl group by id) b
where a.id=b.id and a.time=b.time0) c,
(select a.id,a.statu2 from jl a,(select id,max(time) time0 from jl group by id) b
where a.id=b.id and a.time=b.time0) d
where c.id=d.id
select table.id,a.status1,b.status2
from
table
(select
id,
status1
from
(select min(time) min_time,
id from table
group by id) min,
table
where table.time=min.min_time and table.id=min_time.id(+)) a,
(select
id,
status2
from
(select max(time) max_time,
id from table
group by id) max,
table
where table.time=max.max_time and table.id=max_time.id(+)) b
where table.id=a.id(+) and table.id=b.id(+)
select status1 from table where dt =(select min(time) from table where id ='11') and id ='11';
select status2 from table where dt =(select max(time) from table where id ='11') and id ='11';
两条统计语句
select status1 || '-' || status2 "status1-status2",
count(distinct id) "ID Count"
from t
where t.status1 != t.status2
group by status1 || '-' || status2
union
select 'status1=status2',
count(distinct id)
from t
where t.status1 = t.status2
select statu,count(*) from
(select id,to_char(statu1)||'--'||to_char(statu2) statu from
(select c.id,c.statu1,d.statu2 from
(select a.id,a.statu1 from jl a,(select id,min(time) time0 from jl group by id) b
where a.id=b.id and a.time=b.time0) c,
(select a.id,a.statu2 from jl a,(select id,max(time) time0 from jl group by id) b
where a.id=b.id and a.time=b.time0) d
where c.id=d.id)
)
group by statu