这样子吗?create table score(student_id int,course_id int,grade int) insert into score values(1,1,52) insert into score values(1,2,25) insert into score values(1,3,33) insert into score values(2,1,74) insert into score values(2,2,85) insert into score values(3,1,33)--查找不同课程但成绩相同的学生的学号、课程号和成绩 select * from score a where exists(select 1 from score where course_id<>a.course_id and grade=a.grade)drop table score/*student_id course_id grade ----------- ----------- ----------- 1 3 33 3 1 33(所影响的行数为 2 行)*/
select a.* from 成绩表 a left join 成绩表 b on a.course_id > b.course_id where a.grade = b.grade
declare @tb table (student_id int,course_id int,grade int) insert into @tb select 1,1,50 insert into @tb select 1,2,50 insert into @tb select 1,3,90 insert into @tb select 2,1,50 insert into @tb select 2,2,60select * from @tb tp where exists( select 1 from @tb where student_id=tp.student_id and course_id<>tp.course_id and grade=tp.grade )
create table score(student_id int,course_id int,grade int) insert into score values(1,1,52) insert into score values(1,2,25) insert into score values(1,3,33) insert into score values(2,1,74) insert into score values(2,2,85) insert into score values(3,1,33) go select a.* from score a left join score b on a.course_id <> b.course_id where a.grade = b.gradedrop table score /* student_id course_id grade ----------- ----------- ----------- 3 1 33 1 3 33(所影响的行数为 2 行) */
select * from 成绩表 t where exists(select 1 from ( select 课程号 , 成绩 from 成绩表 group by 课程号 , 成绩 having count(*) > 1 ) where 课程号 = t.课程号 and 成绩 = t.成绩 )
select s1.* from #s s1 inner join #s s2 on s1.grade=s2.grade where s1.course_id<>s2.course_id
我执行完之后,有重复的行出现, select a.* from score a left join score b on a.course_id <> b.course_id where a.grade = b.grade
我已经解决了 select r1.* from result r1 , result r2 where r1.student_id = r2.student_id and r1.course_id <> r2.course_id and r1.grade = r2.grade
select * from 成绩表 as t where not exists (select 1 from 成绩表 where student_id=t.student_id and course_id<>t.course_id and grade<>t.grade)
insert into score values(1,1,52)
insert into score values(1,2,25)
insert into score values(1,3,33)
insert into score values(2,1,74)
insert into score values(2,2,85)
insert into score values(3,1,33)--查找不同课程但成绩相同的学生的学号、课程号和成绩
select *
from score a
where exists(select 1 from score where course_id<>a.course_id and grade=a.grade)drop table score/*student_id course_id grade
----------- ----------- -----------
1 3 33
3 1 33(所影响的行数为 2 行)*/
from 成绩表 a
left join 成绩表 b on a.course_id > b.course_id
where a.grade = b.grade
insert into @tb select 1,1,50
insert into @tb select 1,2,50
insert into @tb select 1,3,90
insert into @tb select 2,1,50
insert into @tb select 2,2,60select * from @tb tp
where exists(
select 1 from @tb where student_id=tp.student_id and course_id<>tp.course_id and grade=tp.grade
)
insert into score values(1,1,52)
insert into score values(1,2,25)
insert into score values(1,3,33)
insert into score values(2,1,74)
insert into score values(2,2,85)
insert into score values(3,1,33)
go
select a.*
from score a
left join score b on a.course_id <> b.course_id
where a.grade = b.gradedrop table score
/*
student_id course_id grade
----------- ----------- -----------
3 1 33
1 3 33(所影响的行数为 2 行)
*/
select * from 成绩表 t where exists(select 1 from
(
select 课程号 , 成绩 from 成绩表 group by 课程号 , 成绩 having count(*) > 1
) where 课程号 = t.课程号 and 成绩 = t.成绩
)
select s1.* from #s s1
inner join #s s2 on s1.grade=s2.grade
where s1.course_id<>s2.course_id
select a.*
from score a
left join score b on a.course_id <> b.course_id
where a.grade = b.grade
select r1.*
from result r1 , result r2
where r1.student_id = r2.student_id and r1.course_id <> r2.course_id
and r1.grade = r2.grade
select * from 成绩表 as t where not exists (select 1 from 成绩表 where student_id=t.student_id and course_id<>t.course_id and grade<>t.grade)