查询没有考试的学生学号,姓名及缺考的课程号,课程名create table student
(
sid int identity primary key,
name nvarchar(50)
)
insert into student
select 'aaa' union
select 'bbb' union
select 'ccc'
go
create table F
(
fid int identity primary key,
fname nvarchar(50)
)
go
insert into F
select '数学' union
select '语文' union
select '英语'
go
create table Score
(
cid int identity primary key,
sid int,
fid int,
score int
)
go
insert into score
select 1,1,60 union
select 1,2,70 union
select 1,3,80 union
select 2,1,90
goselect * from student
select * from f
select * from score
需要结果如下:
sid fid name fname
2 2 bbb 英语
2 3 bbb 语文
3 1 ccc 数学
3 2 ccc 英语
3 3 ccc 语文
我写了一种方法不知道对不对:--select s.sid,s.name,fname
--from student s
--cross join score c
--cross join f
--where
--not exists
--(
--select cid from score where s.sid=score.sid and f.fid=score.fid
--)
--group by s.sid,s.name,fname
--order by sid
请问我得方法对吗?如果对,效率如何?如果不对,错在哪?
还有其他方法吗?尽量多写几种PS:请各位大神测试后贴 答案,小弟已经把表建好了
(
sid int identity primary key,
name nvarchar(50)
)
insert into student
select 'aaa' union
select 'bbb' union
select 'ccc'
go
create table F
(
fid int identity primary key,
fname nvarchar(50)
)
go
insert into F
select '数学' union
select '语文' union
select '英语'
go
create table Score
(
cid int identity primary key,
sid int,
fid int,
score int
)
go
insert into score
select 1,1,60 union
select 1,2,70 union
select 1,3,80 union
select 2,1,90
goselect * from student
select * from f
select * from score
需要结果如下:
sid fid name fname
2 2 bbb 英语
2 3 bbb 语文
3 1 ccc 数学
3 2 ccc 英语
3 3 ccc 语文
我写了一种方法不知道对不对:--select s.sid,s.name,fname
--from student s
--cross join score c
--cross join f
--where
--not exists
--(
--select cid from score where s.sid=score.sid and f.fid=score.fid
--)
--group by s.sid,s.name,fname
--order by sid
请问我得方法对吗?如果对,效率如何?如果不对,错在哪?
还有其他方法吗?尽量多写几种PS:请各位大神测试后贴 答案,小弟已经把表建好了
from student s
cross join f
where
not exists
(
select cid from score where s.sid=score.sid and f.fid=score.fid
)
group by s.sid,s.name,fname
order by sid
(
sid int identity primary key,
name nvarchar(50)
)
insert into student
select 'aaa' union
select 'bbb' union
select 'ccc'
go
create table F
(
fid int identity primary key,
fname nvarchar(50)
)
go
insert into F
select '数学' union
select '语文' union
select '英语'
go
create table Score
(
cid int identity primary key,
sid int,
fid int,
score int
)
go
insert into score
select 1,1,60 union
select 1,2,70 union
select 1,3,80 union
select 2,1,90
goselect m.* from
(
select s.* , f.* from student s , f
) m where not exists(select 1 from score n where n.sid = m.sid and n.fid = m.fid)
drop table student , f , score/*
sid name fid fname
----------- -------------------------------------------------- ----------- --------------------------------------------------
2 bbb 2 英语
2 bbb 3 语文
3 ccc 1 数学
3 ccc 2 英语
3 ccc 3 语文(所影响的行数为 5 行)
*/
where not exists(select 1 from score n where n.sid = s.sid and n.fid = f.fid)
drop table student , f , score/*
sid name fid fname
----------- -------------------------------------------------- ----------- --------------------------------------------------
2 bbb 2 英语
2 bbb 3 语文
3 ccc 1 数学
3 ccc 2 英语
3 ccc 3 语文(所影响的行数为 5 行)
*/
在CSDN,我到是还从来没打过酱油.我只是完全看不懂楼主的需求而已.