17,082
社区成员
发帖
与我相关
我的任务
分享求一条分组函数SQL？
ID type time data
1 002 b 2008-8-1 11.000
2 001 a 2008-7-29 50.000
3 003 c 2008-5-1 20.000
4 003 c 2008-1-1 12.000
5 001 a 2008-7-11 50.000
6 002 b 2008-7-28 50.000
得到如下效果
1 001 a 50
2 002 b 50
3 003 c 12
原则： 分完组后得到最早日期对应的值。
...全文
请发表友善的回复…
发表回复
tcl19841002 2008-07-30
- 打赏
- 举报
学习一下
SELECT ID,TYPE,DATE FROM
(
SELECT ID,TYPE,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY TIME) NUM FROM TABLENAME
) WHERE NUM=1
SELECT ID,TYPE,DATE FROM
(
SELECT ID,TYPE,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY TIME) NUM FROM TABLENAME
) WHERE NUM=1
ntalex 2008-07-30
- 打赏
- 举报
楼上的方法是效率最高的
dbcxbj 2008-07-30
- 打赏
- 举报
select id, type, data
from (select id,
type,
data,
row_number() over(partition by id order by time) rn)
where rn = 1
hebo2005 2008-07-30
- 打赏
- 举报
select ID , type , time , data
from
(
select a.*,row_number() over (partition by type order by time) rn
from a
)
where rn=1
from
(
select a.*,row_number() over (partition by type order by time) rn
from a
)
where rn=1
