求SQL语句：找出所有班级得分最少的记录 谢谢，有说明

[javamy006]

如我有成绩表

ID，班级，名称，得分，....... 后面很多字段
1 一班 张三 90
2 一班 李四 91
3 二班 王五 82
4 二班 小王 79
5 二班 小红 79
...
...
----------------------------------------------------
然后要找出班级得分最少的记录
如结果
ID，班级，名称，得分，....... 后面很多字段
1 一班 张三 90
4 二班 小王 79
5 二班 小红 79
...
...

-------------------------------------------------
谢谢

[划水打酱油的围观党]

select * from table where id in (select min(分数) from table group by 班级)

没测试，不知道行不

[闹铃]

select A.*
from table_name as A
where not exists(select 1 from table_name as B where A.banji=B.banji and B.defen>A.defen)

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-09-23 11:34:10
-- Version:
--      Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64) 
--	Jun 10 2013 20:09:10 
--	Copyright (c) Microsoft Corporation
--	Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([ID] int,[班级] varchar(4),[名称] varchar(4),[得分] int)
insert [huang]
select 1,'一班','张三',90 union all
select 2,'一班','李四',91 union all
select 3,'二班','王五',82 union all
select 4,'二班','小王',79 union ALL
select 5,'二班','小红',79
--------------开始查询--------------------------
SELECT *
FROM huang a
WHERE EXISTS (SELECT 1 FROM (
select [班级],MIN([得分])[得分]
from [huang]
GROUP BY [班级]) b WHERE a.[班级]=b.[班级] AND a.[得分]=b.[得分])
----------------结果----------------------------
/* 
ID          班级   名称   得分
----------- ---- ---- -----------
1           一班   张三   90
4           二班   小王   79
5           二班   小红   79

*/

[ai_li7758521]

SELECT * 
INTO SC
FROM
(
 SELECT 1 as ID,'一班' as Class,'张三' as Name,90 as Score
 UNION ALL SELECT 2,'一班','李四',91
 UNION ALL SELECT 3,'二班','王五',82
 UNION ALL SELECT 4,'二班','小王',79
 UNION ALL SELECT 5,'二班','小红',79
 ) T


;WITH S AS 
(
	SELECT Class,MIN(Score) as Score
	FROM SC
	GROUP BY Class
)

SELECT SC.*
FROM SC JOIN S 
ON SC.Class=S.Class and SC.Score=S.Score
ORDER BY ID

--------------------------------
ID	Class	Name	Score
1	一班	张三	90
4	二班	小王	79
5	二班	小红	79
---------------------------------

[Andy__Huang]

select a.*
from tb a
inner join 
(
select 班级,名称,min(得分) 得分 from tb group by 班级,名称
)b on a.班级=b.班级 and a.名称=b.名称 and a.得分=b.得分