#1、查询“01”课程比“02”课程成绩高的学生的信息
create table student(
id int auto_increment primary key,
sno int(5) not null unique,
sname varchar(10) not null,
sage int(5) not null default 1,
ssex enum('男','女'),
tno int(5) not null
);create table score(
id int auto_increment primary key,
sno int(5) not null,
cno int(5) not null,
score float(4,1)
);select tmp2.sno,st.sname
from (
select tmp1.sno sno
from(
select sno,
score
from score where cno='01'
)tmp,
(
select sno,
score
from score where cno='02'
)tmp1
where tmp.score>tmp1.score
and tmp.sno=tmp1.sno;)tmp2,
student st
where st.sno = tmp2.sno;
create table student(
id int auto_increment primary key,
sno int(5) not null unique,
sname varchar(10) not null,
sage int(5) not null default 1,
ssex enum('男','女'),
tno int(5) not null
);create table score(
id int auto_increment primary key,
sno int(5) not null,
cno int(5) not null,
score float(4,1)
);select tmp2.sno,st.sname
from (
select tmp1.sno sno
from(
select sno,
score
from score where cno='01'
)tmp,
(
select sno,
score
from score where cno='02'
)tmp1
where tmp.score>tmp1.score
and tmp.sno=tmp1.sno;)tmp2,
student st
where st.sno = tmp2.sno;
from (
select tmp1.sno sno
from(
select sno,
score
from score where cno='01'
)tmp,
(
select sno,
score
from score where cno='02'
)tmp1
where tmp.score>tmp1.score
and tmp.sno=tmp1.sno;)tmp2,
student st
where st.sno = tmp2.sno;
为什么运行不成功呢?错在哪里帮忙看一下
select tmp2.sno,st.sname
from (
select tmp1.sno sno
from(
select sno,
score
from score where cno='01'
)tmp,
(
select sno,
score
from score where cno='02'
)tmp1
where tmp.score>tmp1.score
and tmp.sno=tmp1.sno)tmp2,
student st
where st.sno = tmp2.sno;
T-SQL完全不需要;