create table 选课
(
学号 varchar(10),
课程号 varchar(10),
成绩 int
)insert into 选课 values('1','a',99)
insert into 选课 values('1','b',99)
insert into 选课 values('1','c',99)
insert into 选课 values('1','d',99)
insert into 选课 values('2','a',93)
insert into 选课 values('3','a',94)
insert into 选课 values('4','a',95)
insert into 选课 values('5','a',99)
insert into 选课 values('2','b',93)
insert into 选课 values('3','b',94)
insert into 选课 values('4','b',95)
insert into 选课 values('5','b',99)
insert into 选课 values('2','c',93)
insert into 选课 values('3','c',94)
insert into 选课 values('4','c',95)
insert into 选课 values('5','c',99)
insert into 选课 values('2','d',93)
insert into 选课 values('3','d',94)
insert into 选课 values('4','d',99)
insert into 选课 values('5','d',91)select 学号
from 选课 t1 join
(select 课程号,max(成绩) 成绩
from 选课
group by 课程号) t2
on t1.课程号=t2.课程号 and t1.成绩=t2.成绩
group by 学号
having count(*)=(select count(distinct 课程号) from 选课)drop table 选课
(
学号 varchar(10),
课程号 varchar(10),
成绩 int
)insert into 选课 values('1','a',99)
insert into 选课 values('1','b',99)
insert into 选课 values('1','c',99)
insert into 选课 values('1','d',99)
insert into 选课 values('2','a',93)
insert into 选课 values('3','a',94)
insert into 选课 values('4','a',95)
insert into 选课 values('5','a',99)
insert into 选课 values('2','b',93)
insert into 选课 values('3','b',94)
insert into 选课 values('4','b',95)
insert into 选课 values('5','b',99)
insert into 选课 values('2','c',93)
insert into 选课 values('3','c',94)
insert into 选课 values('4','c',95)
insert into 选课 values('5','c',99)
insert into 选课 values('2','d',93)
insert into 选课 values('3','d',94)
insert into 选课 values('4','d',99)
insert into 选课 values('5','d',91)select 学号
from 选课 t1 join
(select 课程号,max(成绩) 成绩
from 选课
group by 课程号) t2
on t1.课程号=t2.课程号 and t1.成绩=t2.成绩
group by 学号
having count(*)=(select count(distinct 课程号) from 选课)drop table 选课
解决方案 »
- 请教 查询结果中的一列加上自增编号再插入到一个表中
- sql语句在eclipse里出错
- MSSQL数据库表设计问题!
- 链接数据库表结构变更后问题
- sql语句,详情请进
- sql server日志显示如下:调度程序 0 似乎已被挂起。SPID 0,ECID 0,UMS 上下文 0x27286FC0,请帮忙解决
- 小问题,快来帮帮我呀
- 救我:odbc连sql server问题
- 关于触发器
- sql server 2000 小问题。
- 触发器问题,关于inserted表,和deleted表
- 我觉得SQL的联机帮助里面的函数按字母的顺序一排列好像不是几合适的. 好像我要学习字符串函数, 我没理由每个函数打开来看一下它是不是字
from 选课 t1 join
(select 课程号,max(成绩) 成绩
from 选课
group by 课程号) t2
on t1.课程号=t2.课程号 and t1.成绩=t2.成绩
group by 学号
having count(*)=(select count(distinct 课程号) from 选课 where 学号=t1.学号)
(
学号 varchar(10),
课程号 varchar(10),
成绩 int
)insert into 选课 values('1','a',99)
insert into 选课 values('1','b',99)
insert into 选课 values('1','c',99)
insert into 选课 values('1','d',99)
insert into 选课 values('2','a',93)
insert into 选课 values('3','a',94)
insert into 选课 values('4','a',95)
insert into 选课 values('5','a',99)
insert into 选课 values('2','b',93)
insert into 选课 values('3','b',94)
insert into 选课 values('4','b',95)
insert into 选课 values('5','b',99)
insert into 选课 values('2','c',93)
insert into 选课 values('3','c',94)
insert into 选课 values('4','c',95)
insert into 选课 values('5','c',99)
insert into 选课 values('2','d',93)
insert into 选课 values('3','d',94)
insert into 选课 values('4','d',99)
insert into 选课 values('5','d',91)select 学号 ,课程号 , 成绩 from 选课 where 课程号 = 'a'select * from 选课 A where 成绩 in ( select top 1 成绩 from 选课 where 课程号= A.课程号 order by 成绩 desc )
order by 成绩 desc , 课程号
学号 课程号 成绩
---------- ---------- -----------
1 a 99
5 a 99
5 b 99
1 b 99
1 c 99
5 c 99
4 d 99
1 d 99