34,876
社区成员
发帖
与我相关
我的任务
分享*****午夜没有激情*****,来这里求一条SQL语句~~~
表:
create table tb(A smalldatetime ,B varchar(20),C Float)
------------------------------------------
数据:
2009/03/01 001 1
2009/03/01 001 2
2009/03/02 001 3
2009/03/03 001 4
2009/03/04 002 5
2009/03/05 002 6
2009/03/06 003 7
2009/03/06 003 8
2009/03/06 003 9
2009/03/07 003 10
2009/03/08 001 11
2009/03/08 001 12
...
根据B不同,A 按日期降序排列,取前3条数据,结果如下:
---------------------------------------------
2009/03/03 001 4
2009/03/08 001 11
2009/03/08 001 12
2009/03/04 002 5
2009/03/05 002 6
2009/03/06 003 8
2009/03/06 003 9
2009/03/07 003 10
...
求助了...
create table tb(A smalldatetime ,B varchar(20),C Float)
------------------------------------------
数据:
2009/03/01 001 1
2009/03/01 001 2
2009/03/02 001 3
2009/03/03 001 4
2009/03/04 002 5
2009/03/05 002 6
2009/03/06 003 7
2009/03/06 003 8
2009/03/06 003 9
2009/03/07 003 10
2009/03/08 001 11
2009/03/08 001 12
...
根据B不同,A 按日期降序排列,取前3条数据,结果如下:
---------------------------------------------
2009/03/03 001 4
2009/03/08 001 11
2009/03/08 001 12
2009/03/04 002 5
2009/03/05 002 6
2009/03/06 003 8
2009/03/06 003 9
2009/03/07 003 10
...
求助了...
...全文
请发表友善的回复…
发表回复
kaikai_kk 2009-03-17
- 打赏
- 举报
嗯,晓得了,谢谢
百年树人 2009-03-17
- 打赏
- 举报
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 行）
*/dawugui 2009-03-17
- 打赏
- 举报
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 dawugui 2009-03-17
- 打赏
- 举报
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 行）
*/注意你的003有四条数据.
dawugui 2009-03-17
- 打赏
- 举报
select t.* from tb t where a in (select top 3 a from tb where b = t.b order by a desc)
