--未測試 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 )
select * from t1 a where (select count(*) from t1 where amt>a.amt and regon=a.regon)<=1
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
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
补充一下 cust_id 在表中同一个有很多既是 cust_id amt region 000002 12.45 04 000002 34.67 04 你必须要sum一下在做的
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
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
同意N_chow(一劍飄香),不过如果事先还要sum一下的话,用一条语句太勉强了。
试试: 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
上面抄错了: 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)<=1cust_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 行)
cust_id amt region 000002 12.45 04 000002 34.67 04cust_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 行)
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
TO J9998 你的方法很好,但是我还有一个问题 就是如果我要按照AMT的大小排序的话 能够解决么?
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
)
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
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
你那天一个语句,字符串分解的,害得我老人家花了一个多小时!
看完后感觉很好,多谢了!!!!
cust_id 在表中同一个有很多既是
cust_id amt region
000002 12.45 04
000002 34.67 04
你必须要sum一下在做的
(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
(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
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
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)<=1cust_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 行)
000002 12.45 04
000002 34.67 04cust_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 行)
inner join table as b on a.regions<>b.regions
order by atm DESC
你的方法很好,但是我还有一个问题
就是如果我要按照AMT的大小排序的话
能够解决么?