表结构如下
message 表
id(varchar2) receivetime(date) taskid projectid(varchar2)
1 2009-1-1 1 1
2 2009-1-2 1 1
3 2009-1-1 1 2
4 2009-1-3 2 2 现在想根据projectId分组,取出每个projectId里面receivtime的最大时间,并且返回这个表的所有字段,
例如根据以上要求,上面的结果为:
id(varchar2) receivetime(date) taskid projectid(varchar2)
2 2009-1-2 1 1
4 2009-1-3 2 2
怎么弄呀,急急急
message 表
id(varchar2) receivetime(date) taskid projectid(varchar2)
1 2009-1-1 1 1
2 2009-1-2 1 1
3 2009-1-1 1 2
4 2009-1-3 2 2 现在想根据projectId分组,取出每个projectId里面receivtime的最大时间,并且返回这个表的所有字段,
例如根据以上要求,上面的结果为:
id(varchar2) receivetime(date) taskid projectid(varchar2)
2 2009-1-2 1 1
4 2009-1-3 2 2
怎么弄呀,急急急
解决方案 »
- oracle如何导出用户下的表结构???
- ORA-00600: internal error code, arguments: [kkpoStateObject], [3], [], [], [], [
- oracle sql语句问题
- ORACLE Enterprise Console中sysdba账户 如何建立normal账户
- 谁能帮我优化一下这条oracle 的sql语句?谢谢大家!
- 谁帮我看看这条sql是什么意思?
- 请问那里有Oracle的LogMiner下载
- 关于个SQL语句
- 两张大表关联合成一张表时索引问题请教
- solaris下安装oracle时光盘弹不出来?
- bat执行oracle文件问题
- 请教oracle导出txt文件,再用bcp导入sqlserver的问题
select 1,'2009-1-1',1,1
union all
select 2,'2009-1-2',1,1 union all
select 3,'2009-1-1',1,2 union all
select 4,'2009-1-3',2,2 union all
select 5,'2009-1-1',2,2
select *from #message a where not exists(select 1 from #message b where b.receivetime>a.receivetime and b.projectid=a.projectid)
select * from message where receivtime in (
select max(receivtime) from message group by projectId)
如果数据比较大的话可以考虑使用exist
select id,taskid ,projectida,max(date) over (partition by date) from message a;
Select *
From message
Where id In
(Select id
From (Select id, Max(receivetime) From message Group By id) A)
不好意思回答错了,这样写业务上理解错了
可以把所有字段全部加上分组函数:select max(receivtime) ,min(id),min(taskid) from message group by projectId
(select MAX(receivetime) maxdate, projectid from message
GROUP by projectid) b
where a.receivetime=b.maxdate and a.projectid=b.projectid;这个应该可以了,我自己测试了一下。
但是有一个问题,如果同一个工程里面有多条记录的receivetime都是最大的time,不知道你是只取一条记录还是都取?如果是都取的话,这个sql就没有问题了,如果只取1条,那就有问题了。
select 1 id,to_date('2009-01-01','yyyy-mm-dd') receivetime,1 taskid,'1' projectid from dual
union all
select 2 id,to_date('2009-01-02','yyyy-mm-dd') receivetime,1 taskid,'1' projectid from dual
union all
select 3 id,to_date('2009-01-01','yyyy-mm-dd') receivetime,1 taskid,'2' projectid from dual
union all
select 4 id,to_date('2009-01-03','yyyy-mm-dd') receivetime,2 taskid,'2' projectid from dual)
select id,receivetime,taskid, projectid from (
select id,receivetime,taskid, projectid,row_number() over(partition by projectid order by receivetime desc) rn from temp
) where rn = 1