如果有主键用 min()加 group by 最简单,如果没有,用下面的脚本试试SELECT DISTINCT (SELECT TOP 1 学号
FROM tb tb1
WHERE tb1.学号 = tb.学号) AS 学号,
(SELECT TOP 1 姓别
FROM tb tb1
WHERE tb1.学号 = tb.学号) AS 姓别,
(SELECT TOP 1 学科
FROM tb tb1
WHERE tb1.学号 = tb.学号) AS 学科,
(SELECT TOP 1 成绩
FROM tb tb1
WHERE tb1.学号 = tb.学号) AS 成绩
FROM tb
FROM tb tb1
WHERE tb1.学号 = tb.学号) AS 学号,
(SELECT TOP 1 姓别
FROM tb tb1
WHERE tb1.学号 = tb.学号) AS 姓别,
(SELECT TOP 1 学科
FROM tb tb1
WHERE tb1.学号 = tb.学号) AS 学科,
(SELECT TOP 1 成绩
FROM tb tb1
WHERE tb1.学号 = tb.学号) AS 成绩
FROM tb
into #t
from tableselect 学号, 姓名, 学科, 成绩
from #t a
where not exists(select * from #t where 学号=a.学号 and id<a.id)
drop table #t
Inner Join (Select 学号,姓别,Min(学科) As 学科 from TableName Group By 学号,姓别) B
On A.学号=B.学号 And A.姓别=B.姓别
http://blog.csdn.net/zlp321002/archive/2005/01/12/249819.aspx
select * , id=identity(int,1,1)
into #t
from tableselect 学号, 姓别, 学科, 成绩
from #t a
where (select count(*) from #t where 学号=a.学号 and id<a.id)<1
select * from table1 a where 学科 in (select max(学科) from table1 where a.学号=学号 and a.姓别=姓别) order by 学号