sql 如何获取表中，每组数据的前三行？

[zhanmincode]

如何将 ATable 表，如下
A B C
aa sx 1000
aa zh 900
bb jj 1100
bb zh 500
bb gg 400
bb wd 300
bb sh 150
cc sh 1500

转换为BTable表，如下
A B C
aa sx 1000
aa zh 900
bb jj 1100
bb zh 500
bb gg 400
cc sh 1500

即不满三条的数据，全部取出，超过3条的数据，取三条，后面滤去，诶，水平有限，想不出来

[WWWWA]

select t.* from
(select Atable.*,row_number() over(partition by A order by C desc) rn from Atable) t
where rn<=3
在SQL ANYWHERE11下是可以的

select a.a,a.b,a.c from ttd a left join ttd b on a.a=b.a and a.c<=b.c
group by a.a,a.b,a.c having count(b.a)<=3
order by a.a,a.c desc

[ACMAIN_CHM]

select p.a.p.b.p.c
from ATable p inner join ATable q on p.a=q.a and ( p.c<q.c  or (p.c=q.c and p.b<=q.b))
group p.a.p.b.p.c
having count(*)<=3

[chenqingyu]

select t.* from 
   (select Atable.*,row_number() over(partition by A order by C desc) rn from Atable) t 
where rn<=3

建议LZ去学习一下分析函数,很好用.

[zhanmincode]

不好意思，忘了说明，是sybase的数据库下，好像函数不一样的

[wwwwb]

楼主没有说明 在C重复的情况下怎么处理

[aohan]

create table t1( a varchar(8),b varchar(8), c int)
insert into t1
select 'aa',  'sx',  1000 union 
select 'aa',  'zh',    900 union 
select 'bb',  'jj',    1100 union 
select 'bb',  'zh',    500 union 
select 'bb',  'gg',    400 union 
select 'bb',  'wd',    300 union 
select 'bb',  'sh',    150 union 
select 'cc',  'sh',    1500 

--select * From t1

select  * from [t1] T  where b in(
select  top 3 b from  [t1]  where a=t.a order by [a] 
) order by a 

drop table t1


/*

(8 行受影响)
a        b        c
-------- -------- -----------
aa       sx       1000
aa       zh       900
bb       gg       400
bb       jj       1100
bb       sh       150
cc       sh       1500

(6 行受影响)



*/

[zhanmincode]

[Quote=引用 3 楼 wwwwb 的回复:]
楼主没有说明 在C重复的情况下怎么处理
[/Quote]
还是取前三条啊，第三条以后并列的记录去掉

[delphi_abc]

我试过了，怎么不行啊