这样的 sql 查询有谁会写？100分

[martinloyee]

表结构和数据：
stid clsclass clsid
1000 3 48
1000 2 52
1000 2 67
1000 3 15
900 2 68

要求：
1、以stid分组
2、clsclass取组中的最大值
3、clsid取组中clsclass为最大值得任何一条记录均可。

上表选取结果应为：
stid clsclass clsid
1000 3 48
900 2 68
或者
stid clsclass clsid
1000 3 15
900 2 68

该查询如何写？各位大人分享一下。

[martinloyee]

select stid,max(clsclass) as clsclass,(select top 1 clsid from #t a where a.stid=#t.stid and a.clsclass=max(#t.clsclass)) as clsid from #t group by stid;

[marco08]

create table T(stid int, clsclass int, clsid int)
insert T select 1000, 3, 48
union all select 1000, 2, 52
union all select 1000, 2, 67
union all select 1000, 3, 15
union all select 900, 2, 68

select stid, clsclass, clsid=min(clsid) from
(
select * from T as tmp
where not exists(select 1 from T where stid=tmp.stid and clsclass>tmp.clsclass)
) tmp group by stid, clsclass

--result
stid clsclass clsid
----------- ----------- -----------
900 2 68
1000 3 15

(2 row(s) affected)

[martinloyee]

marco08(天道酬勤) 的效率比较高！

[青锋-SS]

select stid,max(clsclass) from table1 group by stid;

[青锋-SS]

select stid,max(clsclass) as clsclass,(select clsid from table1 a where a.stid=table1.stid and a.clsclass=max(table1.clsclass)) as clsid from table1 group by stid;

[青锋-SS]

select stid,max(clsclass) as clsclass,(select max(clsid) from table1 a where a.stid=table1.stid and a.clsclass=max(table1.clsclass)) as clsid from table1 group by stid;

或:

select stid,min(clsclass) as clsclass,(select max(clsid) from table1 a where a.stid=table1.stid and a.clsclass=max(table1.clsclass)) as clsid from table1 group by stid;

或:

select stid,max(clsclass) as clsclass,(select top 1clsid from table1 a where a.stid=table1.stid and a.clsclass=max(table1.clsclass)) as clsid from table1 group by stid;