--给个例子参考
--查询每门课程的前2名成绩 CREATE TABLE StudentGrade(
stuId CHAR(4), --学号
subId INT, --课程号
grade INT, --成绩
PRIMARY KEY (stuId,subId)
)
GO
--表中数据如下
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('001',1,97);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('001',2,50);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('001',3,70);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('002',1,92);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('002',2,80);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('002',3,30);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('003',1,93);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('003',2,95);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('003',3,85);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('004',1,73);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('004',2,78);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('004',3,87);
GO
/*
要查询每门课程的前2名成绩
001 1 97
003 1 93
003 2 95
002 2 80
004 3 87
003 3 85
如何实现?
*/
--查看数据
select * from StudentGrade
--假如出现并列时,也只取两个同学的话。
--方法一:
select distinct *
from studentgrade as t1
where stuid in
(select top 2 stuid
from studentgrade as t2
where t1.subid=t2.subid
order by t2.grade desc)
order by subid, grade desc--方法二:
select * from StudentGrade a where (select count(1) from studentGrade where subId=a.subId and grade>=a.grade)<=2--方法三:
select * from StudentGrade t
where (select count(1) from StudentGrade where subid=t.subid and grade>t.grade)<=1
order by subId,grade desc--结果
/*
stuId subId grade
----- ----------- -----------
001 1 97
003 1 93
003 2 95
002 2 80
004 3 87
003 3 85(6 row(s) affected)
*/drop table StudentGrade
--查询每门课程的前2名成绩 CREATE TABLE StudentGrade(
stuId CHAR(4), --学号
subId INT, --课程号
grade INT, --成绩
PRIMARY KEY (stuId,subId)
)
GO
--表中数据如下
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('001',1,97);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('001',2,50);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('001',3,70);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('002',1,92);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('002',2,80);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('002',3,30);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('003',1,93);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('003',2,95);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('003',3,85);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('004',1,73);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('004',2,78);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('004',3,87);
GO
/*
要查询每门课程的前2名成绩
001 1 97
003 1 93
003 2 95
002 2 80
004 3 87
003 3 85
如何实现?
*/
--查看数据
select * from StudentGrade
--假如出现并列时,也只取两个同学的话。
--方法一:
select distinct *
from studentgrade as t1
where stuid in
(select top 2 stuid
from studentgrade as t2
where t1.subid=t2.subid
order by t2.grade desc)
order by subid, grade desc--方法二:
select * from StudentGrade a where (select count(1) from studentGrade where subId=a.subId and grade>=a.grade)<=2--方法三:
select * from StudentGrade t
where (select count(1) from StudentGrade where subid=t.subid and grade>t.grade)<=1
order by subId,grade desc--结果
/*
stuId subId grade
----- ----------- -----------
001 1 97
003 1 93
003 2 95
002 2 80
004 3 87
003 3 85(6 row(s) affected)
*/drop table StudentGrade
create table a
(id int)
create table b
(id int,
name char(1))
go
insert a
values(1)
insert a
values(2)
insert a
values(3)
go
insert b
values(1,'A')
insert b
values(1,'B')
insert b
values(1,'C')
insert b
values(1,'D')
insert b
values(1,'R')
insert b
values(2,'E')
insert b
values(2,'F')
insert b
values(2,'G')
insert b
values(2,'J')
insert b
values(3,'H')
GO
--------------------------------------------------
--语句如下
select name from b bb
where name in (select top 3 name from b where b.id = bb.id)
go--结果
A
B
C
E
F
G
H
select name from b bb
where name in (select top 3 name from b where b.id = bb.id) and id in (select * from a)
select name from b bb
where name in (select top 3 name from b where b.id = bb.id)
go,这个是好的,不过 --补充一下
select name from b bb
where name in (select top 3 name from b where b.id = bb.id) and id in (select * from a)
这个有点问题,提示说 服务器: 消息 116,级别 16,状态 1,行 1
当没有用 EXISTS 引入子查询时,在选择列表中只能指定一个表达式。
where name in (select top 3 name from b where b.id = bb.id) and id in (select name from a)