create database cs
create table s
(
sid int,
workid int,
times varchar(20)
)
insert into s values (1,1,'2008-01-03')
insert into s values (2,1,'2008-01-04')
insert into s values (3,1,'2008-01-05')
insert into s values (4,2,'2008-01-04')
insert into s values (5,2,'2008-01-05')
insert into s values (6,2,'2008-01-06')
select * from s我要查出 sid = 3 和6 的这2调数据 要怎么写
也就是 根据 workid 的不同 查出 times 最大的那条 数据
在 mysql 中 可以 用 order by + group by 查出来 在这 oracle 中 怎么查 我是新手哦
create table s
(
sid int,
workid int,
times varchar(20)
)
insert into s values (1,1,'2008-01-03')
insert into s values (2,1,'2008-01-04')
insert into s values (3,1,'2008-01-05')
insert into s values (4,2,'2008-01-04')
insert into s values (5,2,'2008-01-05')
insert into s values (6,2,'2008-01-06')
select * from s我要查出 sid = 3 和6 的这2调数据 要怎么写
也就是 根据 workid 的不同 查出 times 最大的那条 数据
在 mysql 中 可以 用 order by + group by 查出来 在这 oracle 中 怎么查 我是新手哦
where not exists(select * from s where workid=t.workid and times>t.times)
select sid,workid,times
from
(select sid,workid,times,row_number() over(partition by wordid order times desc) rn
from s)
where rn=1--or
select sid,workid,times
from s t
where not exists(select 1 from s t2 where t.workid=t2.workid and t.times<t2.times)
SQL> select sid,workid,times
2 from s t
3 where not exists
4 (select 1 from s t2
5 where t.workid=t2.workid and
6 t.times<t2.times);
/*
SID WORKID TIMES
------------------ --------------------------------------- --------------------
3 1 2008-01-05
6 2 2008-01-06
*/SQL> select * from s t
2 where not exists(select * from s where workid=t.workid and times>t.times);
/*
SID WORKID TIMES
-------------------- --------------------------------------- --------------------
3 1 2008-01-05
6 2 2008-01-06
*/