use master
go
if exists(select * from sysdatabases where user=’tempsql’)
drop database tempsql
go
create database tempsql
go
use tempsql
create table t_table1
(
t_name varchar(50), — 名字
t_cat char(4), — 科目
t_score int — 成绩
)
delete t_table1
insert into t_table1 values(‘p1′,’c1′,44)
insert into t_table1 values(‘p2′,’c1′,87)
insert into t_table1 values(‘p3′,’c1′,65)
insert into t_table1 values(‘p4′,’c1′,29)
insert into t_table1 values(‘p1′,’c2′,13)
insert into t_table1 values(‘p2′,’c2′,32)
insert into t_table1 values(‘p3′,’c2′,44)
insert into t_table1 values(‘p4′,’c2′,34)
insert into t_table1 values(‘p1′,’c3′,35)
insert into t_table1 values(‘p2′,’c3′,56)
insert into t_table1 values(‘p3′,’c3′,43)
insert into t_table1 values(‘p4′,’c3′,23)
select * from t_table1

–求每科成绩最高分同学的姓名
–ylb:解答
–pubdate:2012-7-3
select t_name from t_table1 t where t_score =(select MAX(t_score) from t_table1 where t_cat=t.t_cat)