110,536
社区成员
发帖
与我相关
我的任务
分享
--创建测试环境
create table TableA(sid int,sname NVarchar(50));
create table TableB(id int,name NVarchar(20),fen int,sid int);
insert into TableA
select 1,N'语文 '
union all select 2,N'数学'
union all select 3,N'英文'
union all select 4,N'化学'
union all select 5,N'物理'
insert into TableB
select 1,N'张三',100,1
union all select 2,N'张三',99,2
union all select 3,N'李四',100,1
union all select 4,N'王五',78,3
union all select 5,N'张三',98,3
union all select 6,N'aa',99,1
union all select 7,N'x',98,1
union all select 8,N'afda',99,1
union all select 9,N'afea',97,1
union all select 10,N'32s',95,1
union all select 11,N'fff',99,2
union all select 12,N'3w',98,2
union all select 13,N'd',98,2
union all select 14,N'fd',97,2
union all select 15,N'df',96,2
union all select 16,N'ffsf',95,2
union all select 17,N'df',71,3
union all select 18,N'xx',72,3
union all select 19,N'yt',73,3
union all select 20,N'yt',80,4
union all select 21,N'ff',98,5
--查询课程表内前3门课程的前5名学生的分数数据
select a.sname,b.name,b.fen from TableB b,TableA a
where a.sid=b.sid and b.sid in(select top 3 sid from tablea)
and b.fen in(select top 5 fen from TableB where sid=b.sid order by fen desc)
order by b.sid asc,b.fen desc
--清除测试环境
drop table tablea;
drop table tableb;
--执行结果:
/*
sname name fen
语文 张三 100
语文 李四 100
语文 aa 99
语文 afda 99
语文 x 98
数学 张三 99
数学 fff 99
数学 3w 98
数学 d 98
数学 fd 97
英文 张三 98
英文 王五 78
英文 yt 73
英文 xx 72
英文 df 71
*/
--创建测试环境
create table TableA(sid int,sname NVarchar(50));
create table TableB(id int,name NVarchar(20),fen int,sid int);
insert into TableA
select 1,N'语文 '
union all select 2,N'数学'
union all select 3,N'英文'
union all select 4,N'化学'
union all select 5,N'物理'
insert into TableB
select 1,N'张三',100,1
union all select 2,N'张三',99,2
union all select 3,N'李四',100,1
union all select 4,N'王五',78,3
union all select 5,N'张三',98,3
union all select 6,N'aa',99,1
union all select 7,N'x',98,1
union all select 8,N'afda',99,1
union all select 9,N'afea',97,1
union all select 10,N'32s',95,1
union all select 11,N'fff',99,2
union all select 12,N'3w',98,2
union all select 13,N'd',98,2
union all select 14,N'fd',97,2
union all select 15,N'df',96,2
union all select 16,N'ffsf',95,2
union all select 17,N'df',71,3
union all select 18,N'xx',72,3
union all select 19,N'yt',73,3
union all select 20,N'yt',80,4
union all select 21,N'ff',98,5
--查询课程表内前3门课程的前5名学生的分数数据
select a.sname,b.name,b.fen from TableB b,TableA a
where a.sid=b.sid and b.sid in(select top 3 sid from tablea)
and b.fen in(select top 5 fen from TableB where sid=b.sid order by fen desc)
order by b.sid asc,b.fen desc
--清除测试环境
drop table tablea;
drop table tableb;
--结果:
/*
sname name fen
语文 aa 99
语文 afda 99
语文 x 98
语文 afea 97
语文 32s 95
数学 3w 98
数学 d 98
数学 fd 97
数学 df 96
数学 ffsf 95
英文 张三 98
英文 王五 78
英文 yt 73
英文 xx 72
英文 df 71
*/
--创建测试环境
create table TableA(sid int,sname NVarchar(50));
create table TableB(id int,name NVarchar(20),fen int,sid int);
insert into TableA
select 1,N'语文 '
union all select 2,N'数学'
union all select 3,N'英文'
union all select 4,N'化学'
union all select 5,N'物理'
insert into TableB
select 1,N'张三',100,1
union all select 2,N'张三',99,2
union all select 3,N'李四',100,1
union all select 4,N'王五',78,3
union all select 5,N'张三',98,3
union all select 6,N'aa',99,1
union all select 7,N'x',98,1
union all select 8,N'afda',99,1
union all select 9,N'afea',97,1
union all select 10,N'32s',95,1
union all select 11,N'fff',99,2
union all select 12,N'3w',98,2
union all select 13,N'd',98,2
union all select 14,N'fd',97,2
union all select 15,N'df',96,2
union all select 16,N'ffsf',95,2
union all select 17,N'df',71,3
union all select 18,N'xx',72,3
union all select 19,N'yt',73,3
union all select 20,N'yt',80,4
union all select 21,N'ff',98,5
--查询课程表内前3门课程的前5名学生的分数数据
select a.sname,b.name,b.fen from TableB b,TableA a
where a.sid=b.sid and b.sid in(select top 3 sid from tablea)
and b.fen in(select top 5 fen from TableB where sid=b.sid order by fen)
order by b.sid asc,b.fen desc
--清除测试环境
drop table tablea;
drop table tableb;
--结果:
/*
sname name fen
语文 aa 99
语文 afda 99
语文 x 98
语文 afea 97
语文 32s 95
数学 3w 98
数学 d 98
数学 fd 97
数学 df 96
数学 ffsf 95
英文 张三 98
英文 王五 78
英文 yt 73
英文 xx 72
英文 df 71
*/
select t2.id,t2.name,t2.fen,t2.sid from tableb t1,tableb t2
where t1.sid=t2.sid and t1.fen<=t2.fen
and sid in (select top 3 sid from tablea)
group by t2.id,t2.name,t2.fen,t2.sid
having count(*)<=5
SELECT * FROM
(SELECT TOP 5 * FROM
(SELECT id,name,fen,T.SNAME FROM TABLEB B
LEFT JOIN (SELECT TOP 3 * FROM TABLEA)AS T ON B.SID=T.SID)AS T1 WHERE SNAME='语文'
ORDER BY fen DESC)AS T1
UNION ALL
SELECT * FROM
(SELECT TOP 5 * FROM
(SELECT id,name,fen,T.SNAME FROM TABLEB B
LEFT JOIN (SELECT TOP 3 * FROM TABLEA)AS T ON B.SID=T.SID)AS T1 WHERE SNAME=' 数学 '
ORDER BY fen DESC)AS T1
UNION ALL
SELECT * FROM
(SELECT TOP 5 * FROM
(SELECT id,name,fen,T.SNAME FROM TABLEB B
LEFT JOIN (SELECT TOP 3 * FROM TABLEA)AS T ON B.SID=T.SID)AS T1 WHERE SNAME=' 英文 '
ORDER BY fen DESC)AS T1
SELECT TOP 5 * FROM (SELECT id,name,fen,T.SNAME FROM TABLEB B LEFT JOIN (SELECT TOP 3 * FROM TABLEA)AS T ON B.SID=T.SID)AS T1 WHERE SNAME='语文' ORDER BY fen DESC
select t2.id,t2.name,t2.fen,t2.sid from tableb t1,tableb t2
where t1.id=t2.id and t1.fen<=t2.fen
and id in (select top 3 id from tablea)
group by t2.id,t2.name,t2.fen,t2.sid
having count(*)<=5