表结构如下
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
怎么弄呀,急急急
from tmp_message a
group by projectid
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)
--以上环境MSSQL.不好意思!
Select *
From message
Where id In
(Select id
From (Select id, Max(receivetime) From message Group By id) A)