数据库 oracle。
表结构:
create table test (
code varchar2(10),
sdate date
)数据:
---------code--------dates-------
1 111 2010-6-1
2 111 2010-6-4
3 111 2010-6-16
4 222 2010-6-2
5 222 2010-6-23
6 333 2010-6-8
7 333 2010-6-16
8 444 2010-6-22
按code字段分组,选出每组最大两个日期的记录,不足两条的取一条。如按以上数据,查出的结果应该如下:
---------code--------dates-------
111 2010-6-4
111 2010-6-16
222 2010-6-2
222 2010-6-23
333 2010-6-8
333 2010-6-16
444 2010-6-22求sql,oracle的。
表结构:
create table test (
code varchar2(10),
sdate date
)数据:
---------code--------dates-------
1 111 2010-6-1
2 111 2010-6-4
3 111 2010-6-16
4 222 2010-6-2
5 222 2010-6-23
6 333 2010-6-8
7 333 2010-6-16
8 444 2010-6-22
按code字段分组,选出每组最大两个日期的记录,不足两条的取一条。如按以上数据,查出的结果应该如下:
---------code--------dates-------
111 2010-6-4
111 2010-6-16
222 2010-6-2
222 2010-6-23
333 2010-6-8
333 2010-6-16
444 2010-6-22求sql,oracle的。
SELECT code, sdate
FROM (SELECT code, sdate, row_number() over (PARTITION BY code ORDER BY sdate DESC) rn
FROM test)
WHERE rn <= 2;
SELECT code, sdate
FROM (SELECT code, sdate, row_number() over (PARTITION BY code ORDER BY sdate DESC) rn
FROM test)
WHERE rn <= 2;
select code,dates
from(select code,dates,row_number() over(partition by code order by dates desc) rn
from test) t
where rn=1--通用的
select code,dates a from test where exists (select 1 from test where a.code=code and a.dates>dates)
现在不懂就先学吧
迟早会用到了
select code,dates
from(select code,dates,row_number() over(partition by code order by dates desc) rn
from test) t
where rn<=1
mssql 跟oracle 是可以通用的
select code,dates
from(select code,dates,row_number() over(partition by code order by dates desc) rn
from test) t
where rn<=2
mssql 跟oracle 是可以通用的