s(sId,sName) sc (sId,cId,Grade) c(cId,cName) ------------------------------------------------- select s.sId, s.sName, a.EnglishGrade, b.MathGrade from s join (select sc.sId, sc.Grade as EnglishGrade from sc join c on sc.cId = c.cId and c.cName = '英语') a on s.sId = a.sId join (select sc.sId, sc.Grade as MathGrade from sc join c on sc.cId = c.cId and c.cName = '数学') b on s.Id = b.sId where a.EnglishGrade > b.MathGrade
select a.s from (select s,grade from sc,c where sc.c=c.c and c.cname='英语' ) a, (select s,grade from sc,c where sc.c=c.c and c.cname='英语') b where a.s=b.s and isnull(a.grade,0)-isnull(b.grage,0)>0
select s.sname from sc a inner join s where c=(select c from c where cname='英语') and grade>(select grade from sc b where a.s=b.s and c=(select c from c where cname='数学'))
select s.sname from sc a inner join s on a.s=s.s where c=(select c from c where cname='英语') and grade>(select grade from sc b where a.s=b.s and c=(select c from c where cname='数学'))
select name from s where s in (select s from (select a.* from sc a,c b where a.c=b.c and cname='英语')a , (select a.c,a.grade from sc a,c b where a.c=b.c and cname='数学')b where a.c=b.c and a.grade>b.grade)
create table s(s int,sname nvarchar(30)) insert into s(s,sname) select 1,'张三' union select 2,'李四'create table c(c int,cname nvarchar(30)) insert into c(c,cname) select 1,'英语' union select 2,'数学'create table sc(s int,c int,grade int) insert into sc(s,c,grade) select 1,1,50 union select 1,2,60 union select 2,1,70 union select 2,2,50select sname from s where s in(select distinct s from sc t where (select grade from sc where sc.s = t.s and c = (select c from c where cname = '英语')) - (select grade from sc where sc.s = t.s and c = (select c from c where cname = '数学')) > 0)
-------------------------------------------------
select s.sId, s.sName, a.EnglishGrade, b.MathGrade
from s join (select sc.sId, sc.Grade as EnglishGrade
from sc join c on sc.cId = c.cId and c.cName = '英语') a on s.sId = a.sId
join (select sc.sId, sc.Grade as MathGrade
from sc join c on sc.cId = c.cId and c.cName = '数学') b on s.Id = b.sId
where a.EnglishGrade > b.MathGrade
from (select s,grade from sc,c where sc.c=c.c and c.cname='英语' ) a,
(select s,grade from sc,c where sc.c=c.c and c.cname='英语') b
where a.s=b.s and isnull(a.grade,0)-isnull(b.grage,0)>0
where c=(select c from c where cname='英语')
and grade>(select grade from sc b where a.s=b.s and c=(select c from c where cname='数学'))
where c=(select c from c where cname='英语')
and grade>(select grade from sc b where a.s=b.s and c=(select c from c where cname='数学'))
where s in
(select s from
(select a.* from sc a,c b
where a.c=b.c and cname='英语')a ,
(select a.c,a.grade from sc a,c b
where a.c=b.c and cname='数学')b
where a.c=b.c and a.grade>b.grade)
insert into s(s,sname)
select 1,'张三' union
select 2,'李四'create table c(c int,cname nvarchar(30))
insert into c(c,cname)
select 1,'英语' union
select 2,'数学'create table sc(s int,c int,grade int)
insert into sc(s,c,grade)
select 1,1,50 union
select 1,2,60 union
select 2,1,70 union
select 2,2,50select sname from s where s in(select distinct s
from sc t
where (select grade from sc where sc.s = t.s and c = (select c from c where cname = '英语'))
- (select grade from sc where sc.s = t.s and c = (select c from c where cname = '数学')) > 0)