如何取每个种类的前百分之x的记录？

[garpi]

示例数据（表aa）：
地区 商户 金额
----------------------
A 123 20.0
A 159 20.0
A 456 25.0
A 483 30.0
A 789 40.0
A 597 50.0
B 147 10.0
B 258 20.0
B 369 25.0
B 384 30.0


企图得到的结果（预期结果bb）：
地区 商户 金额
----------------------
A 483 30.0
A 789 40.0
A 597 50.0
B 369 25.0
B 384 30.0

现有表aa，想得到表aa中各地区的商户交易金额排该地区里面前百分之50％的记录。（如bb）
即要：
地区A中金额前百分之50％
地区B中金额前百分之50％
....C..............50％
....D..............50％ 等。。。。。。符合条件的记录。

谢谢

[子陌红尘]

select
t.*
from
aa t
where
t.商户 in(select top 50 percent 商 from aa where 地区=t.地区 order by 金额 desc)

[dawugui]

select id1=identity(int,1,1),* into #t from a
go
select * from #t where id1 in(select top 50 percent from #t group by 地区)

[dawugui]

select id1=identity(int,1,1),* into #t from a
go
select * from #t where id1 in(select top 50 percent from #t group by 地区 order by 地区 desc)

上面是取小的50%,你是要取大的50%

[九斤半]

create table aa(地区 char(1),商户 int,金额 money)
insert aa
select 'A',123,20.0 union all
select 'A',159,20.0 union all
select 'A',456,25.0 union all
select 'A',483,30.0 union all
select 'A',789,40.0 union all
select 'A',597,50.0 union all
select 'B',147,10.0 union all
select 'B',258,20.0 union all
select 'B',369,25.0 union all
select 'B',384,30.0
--select * from aa

select * from AA i
where 商户 in
(
select top 50 percent 商户 from AA where 地区=i.地区 order by 商户 desc
)

drop table aa

[dawugui]

经检查,2楼正确,我的出错.不好意思.

[garpi]

select *
into #res
from #tmp as t
where shop_no in (select top 20 percent shop_no from #tmp where city=t.city
order by amt desc)
order by city,amt desc,shop_no,shop_name

我自己的也写出了，但这种查询运行起来很慢，还以为写错了