--??最好给点数据 说明下表结构
select * from S where exists (select 1 from sc where c.cid=sc.cid)
select * from S where exists (select 1 from sc where c.cid=sc.cid)
解决方案 »
- sql 数据库内容替换
- 库存管理系统如何调整成本呀,请教思路?
- sqlserver有没有像联合外键之类的东西
- 在一张表中有价格字段,类型该设置成什么??是real 还是 float 还是别的 急!~~~~~~
- 两行变成一行怎么做?
- 请问各位大虾一条sql语句,谁能帮我写出来啊~~~~!!!
- 下面的查询该如何写,觉得有点挑战性。
- 从上表查询得到下表,如何写?
- 我的sql为什么总是在运行以后右下角的图标隐藏不见,但点那位置还是有?(sql7)
- 请教一个问题,谢谢! 有关于PB+DB数据库开发的一个问题.
- 这个存储过程出错那里???????我看了半天都没有找到 郁闷!!!!!!!!!!!!
- 如何优化这个十几万数据执行8分钟的sql查询语句
FROM SC, S,C
WHERE SC.SID = S.SID AND C.CID = SC.CID
*
from
S
where
not exists(select 1 from sc where sc.sid=s.sid and cid not in (select cid from c))
汗一个,看错了题目,修改下
SELECT S.SID,S.SNAME
FROM S,
(
SELECT SID,COUNT(1) AS RMARK
FROM SC GROUP BY SID
HAVING (COUNT(1) = SELECT COUNT(1) FROM C) ) LO
WHERE S.SID = LO.SID
declare @a int--所有的课程数
select @a=isnull(count(*),0) from 课程表C
select a.*
from 学生表S a inner join
(select sid,count(cid) as 单个人所选的课程数 from 选课表sc group by sid) b on a.sid=b.sid
where b.单个人所选的课程数=@a
select sid from
(
select sid,count(distinct cid) as numC from sc group by sid
) a
where a.numC=(select count(*) from C)
看语句应该是没理解清楚
这样的insert into S select 1,'张三' union all
select 2,'李四'
insert into C select 1,'语文' union all
select 2,'数学' union all
select 3,'外语'
insert into sc select 1,1 union all
select 1,2 union all
select 1,3 ……--要求查询出全选了语数外3科的学生~~
下面这个OKSELECT S.SID,S.SNAME
FROM
(
SELECT SID,COUNT(1) AS RMARK
FROM SC GROUP BY SID) LO,S
WHERE LO.RMARK = (SELECT COUNT(1) FROM C)
AND LO.SID = S.SID
--Course(C#,Cname,T#) 课程表
--SC(S#,C#,score) 成绩表 IF OBJECT_ID('STU')IS NOT NULL
DROP TABLE STU
GO
IF OBJECT_ID('COURSE')IS NOT NULL
DROP TABLE COURSE
GO
IF OBJECT_ID('SC')IS NOT NULL
DROP TABLE SC
GOcreate table stu (S# varchar(8) ,sname varchar(8))
insert into stu
select 's001', '王'union all
select 's002', '李'union all
select 's003', '齐'union all
select 's004', '田'union all
select 's005', '陈'union all
select 's006', '天'
gocreate table course (C# varchar(8),cname varchar(10),T# varchar(8))
insert into course
select 'C001', '数学','T001'union all
select 'C002', '物理','T002'union all
select 'C003', '化学','T003'union all
select 'C004', '政治','T004'union all
select 'C005', '历史','T005'union all
select 'C006', '语文','T006'
gocreate table sc (s# varchar(8) ,c# varchar(8),score int)
insert into sc
select 'S001','C001',88 UNION ALL
select 'S001','C002',89 UNION ALL
select 'S001','C003',80 UNION ALL
select 'S001','C004',84 UNION ALL
select 'S001','C005',60 UNION ALL
select 'S001','C006',39 UNION ALL
select 'S002','C001',77 UNION ALL
select 'S002','C002',66 UNION ALL
select 'S002','C003',100 UNION ALL
select 'S002','C004',30 UNION ALL
select 'S002','C005',56 UNION ALL
select 'S002','C006',85 UNION ALL
select 'S003','C001',83 UNION ALL
select 'S003','C002',82 UNION ALL
select 'S003','C003',85 UNION ALL
select 'S003','C004',88 UNION ALL
select 'S003','C005',89 UNION ALL
select 'S003','C006',80 UNION ALL
select 'S004','C001',84 UNION ALL
select 'S004','C002',60 UNION ALL
select 'S004','C003',39 UNION ALL
select 'S004','C004',77 UNION ALL
select 'S004','C005',66 UNION ALL
select 'S004','C006',100 UNION ALL
select 'S005','C001',30 UNION ALL
select 'S005','C002',56 UNION ALL
select 'S005','C003',85 UNION ALL
select 'S005','C004',83 UNION ALL
select 'S005','C005',82 UNION ALL
select 'S005','C006',85 UNION ALL
select 'S006','C001',77 UNION ALL
select 'S006','C002',66 UNION ALL
select 'S006','C003',100 UNION ALL
select 'S006','C004',30 UNION ALL
select 'S006','C005',50GOselect * from stu where s# in
(select sc.s# from sc group by s# having count(*)=(select count(*)from course))--s001 王
--s002 李
--s003 齐
--s004 田
--s005 陈