请问这样的ＳＱＬ要怎么写？？

kitecn

表格式
AA  BB
a1  d
a1  f
a1  d
a1  d
b2 h
b2 i
b2 j

要取出ＡＡ列相同值的前三条,按ＢＢ列降序．多谢
结果
AA  BB
a1  f
a1  d
a1  d
b2 h
b2 i
b2 j

订正下。楼下所说正解。是我所说的意思

[ 本帖最后由 kitecn 于 2009-4-24 21:10 编辑 ]

cenalulu

你的需求跟你给出的结果不符合啊，根据你的要求结果应该是：
AA  BB
a1  f
a1  d
a1  d
b2 j
b2 i
b2 h
如果是这样的话，sql如下

select * from msort1 a where 3 > (select count(*) from msort1 as b where b.AA = a.AA and b.BB < a.BB) order by a.AA,a.BB desc

枫影谁用了

select * from msort1 a where 2 > (select count(*) from msort1 as b where b.AA = a.AA and b.BB < a.BB) order by a.AA,a.BB desc

好像应该是2吧？

cenalulu

不是要前三条么？

kitecn

多谢。VFP居然不支持。是我同学要。。。我再研究下。

kitecn

我已导到access中测试。发现好慢

magnet2008

我写了一个存储过程，希望有用。
create procedure topn()
begin
declare var varchar(10);
declare found boolean default true;
declare top cursor for
select m  from temp
group by m
having count(*)>2;
declare continue handler for not found
set found=false;
create temporary table vartable
(one varchar(10),
two varchar(10))
engine=heap;
open top;
fetch top into var;
while found do
insert into vartable
(select * from temp
where m=var
order by 2 desc
limit 3 );
fetch top into var;
end while;
close top;
select * from vartable  ;
end