运动员表:racer(id, name, team) //运动员编号,运动员姓名,所属运动队
成绩表:result(id, game, rank) //运动员编号,参加的项目,取得的名次求:获得第一名最多的运动队的名称。不允许使用top之类的,或者能够指定第n条记录的语法。
成绩表:result(id, game, rank) //运动员编号,参加的项目,取得的名次求:获得第一名最多的运动队的名称。不允许使用top之类的,或者能够指定第n条记录的语法。
--racer
if object_id('racer','u') is not null
drop table racercreate table racer
(
id int primary key,
name nvarchar(20),
team nvarchar(20)
)
go
insert into racer values
(1001,'name1','team1'),
(1002,'name2','team2'),
(1003,'name3','team3'),
(1004,'name4','team4'),
(1005,'name5','team5'),
(1006,'name6','team6')go
--result
if object_id('result','u') is not null
drop table resultcreate table result
(
id int,
game nvarchar(20),
[rank] smallint
)
go
insert into result values
(1001,'game1',1),
(1001,'game2',1),
(1002,'game1',2),
(1003,'game3',1),
(1005,'game3',3),
(1002,'game2',2),
(1003,'game1',3),
(1004,'game3',2)
goselect *From racer
select *From result
--SQL
select A.team,count(B.[rank]) 数量 from racer A,result B
where A.id=B.id and [rank]=1
group by A.team
having COUNT(B.[rank]) =
(select max(Num) from (select (count(B.[rank])) as Num from racer A,result B
where A.id=B.id and [rank]=1
group by A.team) A)--结果集
/*
team 数量
team1 2
*/