34,590
社区成员
发帖
与我相关
我的任务
分享
if object_id('[tb]') is not null drop table [tb]
go
create table tb(A smalldatetime ,B varchar(20),C Float)
insert [tb]
select '2009/03/01','001',1 union all
select '2009/03/01','001',2 union all
select '2009/03/02','001',3 union all
select '2009/03/03','001',4 union all
select '2009/03/04','002',5 union all
select '2009/03/05','002',6 union all
select '2009/03/06','003',7 union all
select '2009/03/06','003',8 union all
select '2009/03/06','003',9 union all
select '2009/03/07','003',10 union all
select '2009/03/08','001',11 union all
select '2009/03/08','001',12
select *
from [tb] t
where (select count(1)+1 from tb where b=t.b and (a>t.a or c>t.c))<=3
order by b,a desc
--测试结果:
/*
A B C
------------------------------------------------------ -------------------- -----------------------------------------------------
2009-03-08 00:00:00 001 11.0
2009-03-08 00:00:00 001 12.0
2009-03-03 00:00:00 001 4.0
2009-03-05 00:00:00 002 6.0
2009-03-04 00:00:00 002 5.0
2009-03-07 00:00:00 003 10.0
2009-03-06 00:00:00 003 9.0
2009-03-06 00:00:00 003 8.0
(所影响的行数为 8 行)
*/
create table tb(A smalldatetime ,B varchar(20),C Float)
insert into tb values('2009/03/01', '001', 1 )
insert into tb values('2009/03/01', '001', 2 )
insert into tb values('2009/03/02', '001', 3 )
insert into tb values('2009/03/03', '001', 4 )
insert into tb values('2009/03/04', '002', 5 )
insert into tb values('2009/03/05', '002', 6 )
insert into tb values('2009/03/06', '003', 7 )
insert into tb values('2009/03/06', '003', 8 )
insert into tb values('2009/03/06', '003', 9 )
insert into tb values('2009/03/07', '003', 10)
insert into tb values('2009/03/08', '001', 11)
insert into tb values('2009/03/08', '001', 12)
go
select t.* from tb t where a in (select top 3 a from tb where b = t.b order by a desc) order by t.b , t.a
/*
A B C
------------------------------------------------------ -------------------- -----------------------------------------------------
2009-03-03 00:00:00 001 4.0
2009-03-08 00:00:00 001 11.0
2009-03-08 00:00:00 001 12.0
2009-03-04 00:00:00 002 5.0
2009-03-05 00:00:00 002 6.0
2009-03-06 00:00:00 003 7.0
2009-03-06 00:00:00 003 8.0
2009-03-06 00:00:00 003 9.0
2009-03-07 00:00:00 003 10.0
(所影响的行数为 9 行)
*/
select m.* from
(
select t.* from tb t where a in (select top 3 a from tb where b = t.b order by a desc)
) m where c in (select top 3 c from
(
select t.* from tb t where a in (select top 3 a from tb where b = t.b order by a desc)
) n where n.b = m.b order by c desc
)
order by m.b , m.a
/*
A B C
------------------------------------------------------ -------------------- -----------------------------------------------------
2009-03-03 00:00:00 001 4.0
2009-03-08 00:00:00 001 11.0
2009-03-08 00:00:00 001 12.0
2009-03-04 00:00:00 002 5.0
2009-03-05 00:00:00 002 6.0
2009-03-06 00:00:00 003 8.0
2009-03-06 00:00:00 003 9.0
2009-03-07 00:00:00 003 10.0
(所影响的行数为 8 行)
*/
drop table tb
create table tb(A smalldatetime ,B varchar(20),C Float)
insert into tb values('2009/03/01', '001', 1 )
insert into tb values('2009/03/01', '001', 2 )
insert into tb values('2009/03/02', '001', 3 )
insert into tb values('2009/03/03', '001', 4 )
insert into tb values('2009/03/04', '002', 5 )
insert into tb values('2009/03/05', '002', 6 )
insert into tb values('2009/03/06', '003', 7 )
insert into tb values('2009/03/06', '003', 8 )
insert into tb values('2009/03/06', '003', 9 )
insert into tb values('2009/03/07', '003', 10)
insert into tb values('2009/03/08', '001', 11)
insert into tb values('2009/03/08', '001', 12)
go
select t.* from tb t where a in (select top 3 a from tb where b = t.b order by a desc) order by t.b , t.a
drop table tb
/*
A B C
------------------------------------------------------ -------------------- -----------------------------------------------------
2009-03-03 00:00:00 001 4.0
2009-03-08 00:00:00 001 11.0
2009-03-08 00:00:00 001 12.0
2009-03-04 00:00:00 002 5.0
2009-03-05 00:00:00 002 6.0
2009-03-06 00:00:00 003 7.0
2009-03-06 00:00:00 003 8.0
2009-03-06 00:00:00 003 9.0
2009-03-07 00:00:00 003 10.0
(所影响的行数为 9 行)
*/
select t.* from tb t where a in (select top 3 a from tb where b = t.b order by a desc)