select score.std_name,course.course_name,score from std,course,score
where std.std_id=score.std_id and course.course_id=score.course_id
where std.std_id=score.std_id and course.course_id=score.course_id
调试欢乐多
a.std_id ,
a.std_name ,
b.course_id ,
c.course_name,
b.score
from
std a,
score b,
course c,
(select std_id,count(*) cnt from score) d,
(select count(course_id) cnt from course) e
where
a.std_id = b.std_id and a.std_id = d.std_id and d.cnt = e.cnt and c.course_id = b.course_id
(std_id varchar(4), std_name varchar(100))create table course
(course_id varchar(4), course_name varchar(100))
create table score
(std_id varchar(4), course_id varchar(4) , chengji int)insert into std
select '0001' , 'alex' union all
select '0002' , 'bill' union all
select '0003' , 'cliton' union all
select '0004' , 'duke'insert into course
select '2001' , 'english' union all
select '2002' , 'math' union all
select '2003' , 'physics'
insert into score
select '0001' , '2001' , 81 union all
select '0002' , '2001' , 91 union all
select '0001' , '2003' , 24 union all
select '0003' , '2002' , 62 union all
select '0001' , '2002' , 54
select distinct std.std_name,course.course_name,score.chengji
from std right join score on std.std_id=score.std_id left join course on score.course_id=course.course_id
where not exists
(select 1 from course where
not exists (select 1 from score where std_id=std.std_id and course_id=course.course_id))-----------------------
alex english 81
alex math 54
alex physics 24(所影响的行数为 3 行)
dbo.score.score, dbo.std.std_id
FROM dbo.std RIGHT OUTER JOIN
dbo.score ON dbo.std.std_id = dbo.score.std_id LEFT OUTER JOIN
dbo.course ON dbo.score.course_id = dbo.course.course_id
ORDER BY dbo.std.std_id
前面写得太复杂了
最经典的做法还是双not exists具体看这里:
http://community.csdn.net/Expert/topic/3696/3696046.xml?temp=.1676905
inner join course as c on b.course_id=c.course_id
where a.std_id in
(select std_id from score group by Std_ID having count(distinct course_id)=(select count(*) from course))测试结果:
std_name course_name score
---------- ---------- ----------
alex english 81
alex math 54
alex physics 24(3 row(s) affected)