select * from 表 T where T.学生ID=(select top 10 学生ID from 表 where T.省=省 and T.市=市 order by 成绩 DESC)
select top 10 * from tb group by 省,市 order by 成绩
DECLARE @T TABLE(PROVINCE VARCHAR(10),CITY VARCHAR(10),NAME VARCHAR(10),SCORE INT) INSERT INTO @T SELECT 'JS','NJ', 'A' , 100 UNION ALL SELECT 'JS','NJ', 'A1', 80 UNION ALL SELECT 'JS','NJ', 'A2', 60 UNION ALL SELECT 'JS','WX', 'B' , 80 UNION ALL SELECT 'JS','WX', 'B1', 90 UNION ALL SELECT 'JS','WX', 'B2', 100 UNION ALL SELECT 'JS','ZJ', 'C' , 70 UNION ALL SELECT 'JS','ZJ', 'C1', 90 UNION ALL SELECT 'JS','ZJ', 'C2', 80 UNION ALL SELECT 'SH','PD', 'D' , 100 UNION ALL SELECT 'SH','PD', 'D1', 80 UNION ALL SELECT 'SH','PD', 'D2', 60 UNION ALL SELECT 'SH','MH', 'E' , 80 UNION ALL SELECT 'SH','MH', 'E1', 100 UNION ALL SELECT 'SH','MH', 'E2', 95 UNION ALL SELECT 'SH','XJH','F' , 70 UNION ALL SELECT 'SH','XJH','F1', 78 UNION ALL SELECT 'SH','XJH','F2', 95 UNION ALL SELECT 'SH','XJH','F3', 100 UNION ALL ......SELECT * FROM @T A WHERE ( SELECT COUNT(*) FROM @T B WHERE B.PROVINCE = A.PROVINCE AND B.CITY = A.CITY AND A.SCORE < B.SCORE ) < 10 ORDER BY 1 , 2 , 4
INSERT INTO @T
SELECT 'JS','NJ', 'A' , 100 UNION ALL
SELECT 'JS','NJ', 'A1', 80 UNION ALL
SELECT 'JS','NJ', 'A2', 60 UNION ALL
SELECT 'JS','WX', 'B' , 80 UNION ALL
SELECT 'JS','WX', 'B1', 90 UNION ALL
SELECT 'JS','WX', 'B2', 100 UNION ALL
SELECT 'JS','ZJ', 'C' , 70 UNION ALL
SELECT 'JS','ZJ', 'C1', 90 UNION ALL
SELECT 'JS','ZJ', 'C2', 80 UNION ALL
SELECT 'SH','PD', 'D' , 100 UNION ALL
SELECT 'SH','PD', 'D1', 80 UNION ALL
SELECT 'SH','PD', 'D2', 60 UNION ALL
SELECT 'SH','MH', 'E' , 80 UNION ALL
SELECT 'SH','MH', 'E1', 100 UNION ALL
SELECT 'SH','MH', 'E2', 95 UNION ALL
SELECT 'SH','XJH','F' , 70 UNION ALL
SELECT 'SH','XJH','F1', 78 UNION ALL
SELECT 'SH','XJH','F2', 95 UNION ALL
SELECT 'SH','XJH','F3', 100 UNION ALL
......SELECT * FROM @T A
WHERE
(
SELECT COUNT(*) FROM @T B WHERE
B.PROVINCE = A.PROVINCE
AND
B.CITY = A.CITY
AND
A.SCORE < B.SCORE
) < 10
ORDER BY 1 , 2 , 4