sql 问题

[VB_support]

我有一个表
有三个字段
cust_id(char(6)) amt(money) region(char(2) p_k(identity,1,1)
000001 10000 01 1
000004 34.23 03 2
000002 234.56 04 3
000006 293.45 01 4
000220 45.9 04 5
000345 354.5 01 6
000231 456.99 01 7
000213 456.23 03 8
002234 90.45 04 9
004565 234.09 03 10


我现在的问题是，怎么样勇sql语句得出这样的结果
cust_id amt region
000001 10000 01
000231 456.99 01
000213 456.23 03
004565 234.09 03
000002 234.56 04
002234 90.45 04

条件就是找出不同region中的amt 排名前两位的cust_id
即客户按照region分类，然后再每个region中得出amt前两位的客户

[VB_support]

问题的关键是region是动态的，你并不清楚region有多少个

[N_chow]

--未測試
SELECT * FROM TableName A
WHERE P_K IN
(
SELECT TOP 2 P_K
FROM TableName
WHERE Cust_ID=a.Cust_ID AND region=a.region
ORDER BY amt DESC
)

[N_chow]

呃～錯了。條件錯了

[j9988]

select * from t1 a where (select count(*) from t1 where amt>a.amt and regon=a.regon)<=1

[N_chow]

SELECT * FROM TableName A
WHERE P_K IN
(
SELECT TOP 2 P_K
FROM TableName
WHERE /*Cust_ID=a.Cust_ID*/ AND region=a.region
ORDER BY amt DESC
)
ORDER BY region,amt DESC

[supsuccess]

select t1.cust_id,t1.amt,t1.region
from tab t1 join tab t2 on (t1.region =t2.region)
where t1.amt<=t2.amt
group by t1.cust_id,t1.amt,t1.region
having count(*)<=2
order by t1.region

[j9988]

N_chow(一劍飄香):兄弟啊!
你那天一个语句,字符串分解的,害得我老人家花了一个多小时!
看完后感觉很好,多谢了!!!!

[VB_support]

补充一下
cust_id 在表中同一个有很多既是
cust_id amt region
000002 12.45 04
000002 34.67 04
你必须要sum一下在做的

[j9988]

select a.* from
(select cust_id,sum(amt) as amt,regon from t1 group by cust_id,regon) a where (select count(*) from
(select cust_id,sum(amt) as amt,regon from t1 group by cust_id,regon) b where a.regno=b.regno and b.amt>a.amd)<=1

[N_chow]

hehe...還這樣哦。

還sum一下，那就這樣吧:)
(1)、
SELECT cust_id,region,MIN(P_K) P_K,SUM(Amt) Amt INTO #tmp
FROM TableName
GROUP BY cust_id,region
(2)、
SELECT * FROM #tmp A
WHERE P_K IN
(
SELECT TOP 2 P_K
FROM #tmp
WHERE /*Cust_ID=a.Cust_ID*/ AND region=a.region
ORDER BY amt DESC
)
ORDER BY region,amt DESC
(3)、
DROP TABLE #tmp
(4)、
呵呵，有點懶:D

[Chiff]

同意N_chow(一劍飄香)，不过如果事先还要sum一下的话，用一条语句太勉强了。

[supsuccess]

试试：
select s.region,s.cust_id,s.amt from
(select region,cust_id,isnull(sum(amt),0) as amt from tab group by region,cust_id) s join
(select region,cust_id,isnull(sum(amt),0) as amt from tab group by region,cust_id) t
on (s.region=t.region)
where s.amt<=t.amt
group by s.region,s.cust_id,s.amt
having count(*)<=2
order by s.region,s.cust_id,s.amt desc

[j9988]

上面抄错了：
select a.* from
(select cust_id,sum(amt) as amt,region from t1 group by cust_id,region) a where (select count(*) from
(select cust_id,sum(amt) as amt,region from t1 group by cust_id,region) b where a.region=b.region and b.amt>a.amt)<=1

cust_id amt region
------- --------------------- ------
000001 10000.0000 01
000231 456.9900 01
000213 456.2300 03
004565 234.0900 03
000002 234.5600 04
002234 90.4500 04

（所影响的行数为 6 行）

[j9988]

cust_id amt region
000002 12.45 04
000002 34.67 04

cust_id amt region
------- --------------------- ------
000001 10000.0000 01
000231 456.9900 01
000213 456.2300 03
004565 234.0900 03
000002 281.6800 04
002234 90.4500 04

（所影响的行数为 6 行）

[cyberwizard]

select top 2 atm ,cust_ID,regions from table as a
inner join table as b on a.regions<>b.regions
order by atm DESC

[VB_support]

TO J9998
你的方法很好，但是我还有一个问题
就是如果我要按照AMT的大小排序的话
能够解决么？