请把帖子移到sql基础类,谢谢 select * from (select row_number() over(partition by 编号 order by cast(日期 as datetime) no,*) from tb) where no<3
select * from tb t where (select count(1) from tb where 编号=t.编号 and date>t.date)<2
select * from tablet t where not exists( select 1 from tablet where t.id=id and t.日期<日期 )
这张图好了;with cte as ( select * ,rn=row_number() over(partition by 编号 order by 日期 desc) ) select * from cte whre rn<=2
use tempdb; /* create table t3 ( 编号 int not null, 日期 nvarchar(10) not null ); insert into t3(编号,日期) values (1,'20110101'), (1,'20110102'), (2,'20110103'), (2,'20110104'), (2,'20110105'), (1,'20110106'), (3,'20110107'), (3,'20110108'), (2,'20110109'), (3,'20110110'), (4,'20110111'); */ select t.编号,t.日期 from ( select 编号,ROW_NUMBER() over(partition by 编号 order by 日期 desc) as [sortnum],日期 from t3 ) as t where t.sortnum < 3;
'ROW_NUMBER' is not a recognized function name. 我用的数据库是mssql2000这里好像不支持ROW_NUMBE
select * from
(select row_number() over(partition by 编号 order by cast(日期 as datetime) no,*) from tb)
where no<3
select 1 from tablet where t.id=id and t.日期<日期
)
(
select * ,rn=row_number() over(partition by 编号 order by 日期 desc)
)
select * from cte whre rn<=2
use tempdb;
/*
create table t3
(
编号 int not null,
日期 nvarchar(10) not null
);
insert into t3(编号,日期)
values
(1,'20110101'),
(1,'20110102'),
(2,'20110103'),
(2,'20110104'),
(2,'20110105'),
(1,'20110106'),
(3,'20110107'),
(3,'20110108'),
(2,'20110109'),
(3,'20110110'),
(4,'20110111');
*/
select t.编号,t.日期
from
(
select 编号,ROW_NUMBER() over(partition by 编号 order by 日期 desc) as [sortnum],日期
from t3
) as t
where t.sortnum < 3;
我用的数据库是mssql2000这里好像不支持ROW_NUMBE