Groupby 查询得到ID

[meceky]

select max(Score) as Score,Province from RZ_ExamineePK
group by Province 有这种一句SQL，我想得到每条数据的ID，咋写呢

1 Id √ √ int 4 10 0
2 OrId int 4 10 0 ((0))
3 OrUid int 4 10 0 ((0))
4 Province int 4 10 0 ((0))
5 City int 4 10 0 ((0))
6 TrueName nvarchar 100 50 0 考生姓名
7 Score decimal 9 18 2 考试分数
8 SubjectLevel int 4 10 0 科目等级
9 SchoolName nvarchar 100 50 0 院校名称
10 Status int 4 10 0 ((0)) 是否发布

获取每个省的最大score..

[--小F--]

select * from  RZ_ExamineePK as t where not exists(select 1 from  RZ_ExamineePK where Province=t.Province and Score>t.Score)

[發糞塗牆]

select *
from RZ_ExamineePK a
where exists (select 1 from (select max(Score) as Score,Province from RZ_ExamineePK
group by Province)b where a.score=b.score and a.province=b.province)

[霜寒月冷]

引用 2 楼 DBA_Huangzj 的回复:

select *
from RZ_ExamineePK a
where exists (select 1 from (select max(Score) as Score,Province from RZ_ExamineePK
group by Province)b where a.score=b.score and a.province=b.province)

请问版版，你敲sql 语句的时候,后面回车自己敲的，还是用工具自动生成的，换行之后看上去蛮舒服的.

[haitao]

再关联回来：

select b.id,a.* from (
select max(Score) as Score,Province from RZ_ExamineePK
group by Province
) a
left join RZ_ExamineePK b on a.score=b.score and a.Province=b.Province

[發糞塗牆]

自己敲的....不过我也用prompt

[發糞塗牆]

引用 3 楼 chz415767975 的回复:

[quote=引用 2 楼 DBA_Huangzj 的回复:]

select *
from RZ_ExamineePK a
where exists (select 1 from (select max(Score) as Score,Province from RZ_ExamineePK
group by Province)b where a.score=b.score and a.province=b.province)

请问版版，你敲sql 语句的时候,后面回车自己敲的，还是用工具自动生成的，换行之后看上去蛮舒服的. [/quote] 工具格式化是这样的

SELECT  *
FROM    RZ_ExamineePK a
WHERE   EXISTS ( SELECT 1
                 FROM   ( SELECT    MAX(Score) AS Score ,
                                    Province
                          FROM      RZ_ExamineePK
                          GROUP BY  Province
                        ) b
                 WHERE  a.score = b.score
                        AND a.province = b.province )