--S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄
--C (C#,CN ) C#,CN 分别代表课程编号、课程名称
--SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩
CREATE TABLE S(
S# VARCHAR(50),
SN VARCHAR(50),
SD VARCHAR(50),
SA VARCHAR(50),
)CREATE TABLE C(
C# VARCHAR(50),
CN VARCHAR(500)
)CREATE TABLE SC(
S# VARCHAR(50),
C# VARCHAR(50),
G VARCHAR(50)
)insert c values ('c1','税收基础')
insert c values ('c2','B')
insert c values ('c3','C')
insert c values ('c4','D')
insert c values ('c5','E')
insert c values ('c6','F')insert s values ('1','a','1a','')
insert s values ('2','b','2b','')
insert s values ('3','c','3c','')
insert s values ('4','d','4d','')
insert s values ('5','e','5e','')
insert s values ('6','f','6f','')insert sc values ('1','c1','80')
insert sc values ('4','c2','60')
insert sc values ('2','c3','0')
insert sc values ('2','c1','70')
insert sc values ('3','c4','90')
insert sc values ('4','c3','88')
insert sc values ('4','c5','84')
insert sc values ('5','c1','80')
insert sc values ('5','c2','60')
insert sc values ('5','c3','0')
insert sc values ('5','c4','90')
insert sc values ('5','c5','84')
insert sc values ('5','c5','59')
insert sc values ('5','c6','100')现在要查询 查询选修全部课程的学员姓名和所属单位,如何写sql各位
--C (C#,CN ) C#,CN 分别代表课程编号、课程名称
--SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩
CREATE TABLE S(
S# VARCHAR(50),
SN VARCHAR(50),
SD VARCHAR(50),
SA VARCHAR(50),
)CREATE TABLE C(
C# VARCHAR(50),
CN VARCHAR(500)
)CREATE TABLE SC(
S# VARCHAR(50),
C# VARCHAR(50),
G VARCHAR(50)
)insert c values ('c1','税收基础')
insert c values ('c2','B')
insert c values ('c3','C')
insert c values ('c4','D')
insert c values ('c5','E')
insert c values ('c6','F')insert s values ('1','a','1a','')
insert s values ('2','b','2b','')
insert s values ('3','c','3c','')
insert s values ('4','d','4d','')
insert s values ('5','e','5e','')
insert s values ('6','f','6f','')insert sc values ('1','c1','80')
insert sc values ('4','c2','60')
insert sc values ('2','c3','0')
insert sc values ('2','c1','70')
insert sc values ('3','c4','90')
insert sc values ('4','c3','88')
insert sc values ('4','c5','84')
insert sc values ('5','c1','80')
insert sc values ('5','c2','60')
insert sc values ('5','c3','0')
insert sc values ('5','c4','90')
insert sc values ('5','c5','84')
insert sc values ('5','c5','59')
insert sc values ('5','c6','100')现在要查询 查询选修全部课程的学员姓名和所属单位,如何写sql各位
解决方案 »
- 查询表中某一列的每个值,第一次出现的所有记录
- @@@@@@@@@@@优化SQL@@@@@@@@@@@@@@@@@@@@@@@
- 存储过程对应的dll(我drop了但是添加的时候不知道哪个过程对应哪个dll了)
- 求一段SQL(刚才的需求有变化)
- sql请教?
- 操作失误,怎么恢复?
- 请问SQL SERVER2000 内如何发送邮件和接收邮件(及附件)?
- 错误7105 严重度 22 描述(消息文本) text、ntext 或 image 节点的页 %1!,槽 %2! 不存在。
- 【问】寻求数据更新的方法
- 使用时间来形成_no,同样时间后面的0001如何加1呢?
- 更新(金额=数量*单价)很慢的问题
- sql里面有些是*号作为参数,这个意思是?
from sc a,s
where a.[s#]=s.[s#]
group by [S#],[C#],sn,sd
having(count(*)=(select cout(*) from c ))
CREATE TABLE C( [C#] VARCHAR(50), CN VARCHAR(500) )
CREATE TABLE SC( [S#] VARCHAR(50), [C#] VARCHAR(50), G VARCHAR(50) ) insert c values ('c1','税收基础')
insert c values ('c2','B')
insert c values ('c3','C')
insert c values ('c4','D')
insert c values ('c5','E')
insert c values ('c6','F') insert s values ('1','a','1a','')
insert s values ('2','b','2b','')
insert s values ('3','c','3c','')
insert s values ('4','d','4d','')
insert s values ('5','e','5e','')
insert s values ('6','f','6f','') insert sc values ('1','c1','80')
insert sc values ('4','c2','60')
insert sc values ('2','c3','0')
insert sc values ('2','c1','70')
insert sc values ('3','c4','90')
insert sc values ('4','c3','88')
insert sc values ('4','c5','84')
insert sc values ('5','c1','80')
insert sc values ('5','c2','60')
insert sc values ('5','c3','0')
insert sc values ('5','c4','90')
insert sc values ('5','c5','84')
insert sc values ('5','c5','59')
insert sc values ('5','c6','100')
select a.[S#],s.sn,s.sd
from sc a,s
where a.[s#]=s.[s#]
group by a.[S#],sn,sd
having(count(distinct [C#])=(select count(*) from c ))
S# sn sd
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
5 e 5e(1 行受影响)
from s
where not exists(
select *
from c
where not exists(
select *
from sc
where s#=s.s#
and c#=c.c#))
注:掌握not exists用法很有好处
select sn,sd
from s
where not exists(
select *
from c
where not exists(
select *
from sc
where s#=s.s# and c#=c.c#))
select s.*
from s,(select s#
from sc
group by s#
having count(distinct c#)=(select count(*) from c )) b
where s.s#=b.s#
from c’表示‘一门课程’,因为是从c中选的嘛,第二个not exists 表示‘不’,下面的select *
from sc
where s#=s.s#
and c#=c.c#表示他选,合起来就是‘他不选’
注:参见王珊的数据库系统概论 第三版 P113
as(
select s#,count(c#) as count1 from (
select distinct s#,c# from sc)tb group by s# )
select sn,sd from s where s# in(select s# from tmp where count1 = (select distinct count(c#) from c) )