declare @s table (s# int,sname varchar(10),age int)
declare @sc table(s# int,c# int,grade int)
declare @c table (c# int,cname varchar(10),teacher varchar(10))
insert @s values (1,'aa',45)
insert @sc values (1,1,79)
insert @sc values (1,2,49)
insert @c values (1,'英语','某人')
insert @c values (2,'数学','人')select sname from @s where s# in (select temp1.s# from (select AB.s#,AB.grade from @c A join @sc AB on A.c#=AB.c# where cname='英语') temp1
join (select AB.s#,AB.grade from @c A join @sc AB on A.c#=AB.c# where cname='数学') temp2
on temp1.s#=temp2.s# where temp1.grade>temp2.grade)
declare @sc table(s# int,c# int,grade int)
declare @c table (c# int,cname varchar(10),teacher varchar(10))
insert @s values (1,'aa',45)
insert @sc values (1,1,79)
insert @sc values (1,2,49)
insert @c values (1,'英语','某人')
insert @c values (2,'数学','人')select sname from @s where s# in (select temp1.s# from (select AB.s#,AB.grade from @c A join @sc AB on A.c#=AB.c# where cname='英语') temp1
join (select AB.s#,AB.grade from @c A join @sc AB on A.c#=AB.c# where cname='数学') temp2
on temp1.s#=temp2.s# where temp1.grade>temp2.grade)
select s.*,a.grade as en,c.grade as ma
from s,sc a,sc b,c c,c d
where s.s# = a.s# and a.c# = b.c# and b.cname = '英语' and
s.s# = c.s# and c.c# = d.c# and d.cname = '数学' and
a.grade > c.grade