我必须根据下表找到结果
学生StudentPapersSelection as sps StudentGroupManagegemt as sgm Internal Data as iars
从学生我需要学生rollno和名称,其中iars的paperid = sps的paperid和iars groupid = sgm组ID,学生ID应该基于前两个事项。
我正在运行的查询是:
select students.rollno, students.name from students,sps,iars,sgm where iars.id=1 and students.studentid=(select studentid from sps where sps.paperid=iars.paperid and iars.id=1) and students.studentid=(select studentid from sgm where sgm.groupid=iars.groupid and iars.id=1) and students.course=iars.courseid and students.semester=iars.semester它说查询返回超过1行。 我讨厌这个问题。
I have to find a result based on the following tables
Students StudentPapersSelection as sps StudentGroupManagegemt as sgm Internal Data as iars
From student I need students rollno and name where iars's paperid = sps's paperid and iars groupid= sgm group id and students id should be based on the previous two things.
The query I am running is:
select students.rollno, students.name from students,sps,iars,sgm where iars.id=1 and students.studentid=(select studentid from sps where sps.paperid=iars.paperid and iars.id=1) and students.studentid=(select studentid from sgm where sgm.groupid=iars.groupid and iars.id=1) and students.course=iars.courseid and students.semester=iars.semesterIt says query returning more than 1 rows. I hate this problem.
最满意答案
我会试着猜:
select students.rollno, students.name from iars, students join sps on students.studentid = sps.studentid join sgm on students.studentid = sgm.studentid where iars.id = 1 and sps.paperid=iars.paperid and sgm.groupid=iars.groupid and students.course = iars.courseid and students.semester = iars.semester假设这样的表:
CREATE TABLE `students` ( `studentid` int(11) NOT NULL AUTO_INCREMENT, `rollno` int(11) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `course` int(11) DEFAULT NULL, `semester` int(11) DEFAULT NULL, PRIMARY KEY (`studentid`) ) ENGINE=InnoDB AUTO_INCREMENT=66820 DEFAULT CHARSET=latin1 CREATE TABLE `sps` ( `studentid` int(11) NOT NULL AUTO_INCREMENT, `paperid` int(11) DEFAULT NULL, PRIMARY KEY (`studentid`) ) ENGINE=InnoDB AUTO_INCREMENT=66820 DEFAULT CHARSET=latin1 CREATE TABLE `sgm` ( `studentid` int(11) NOT NULL AUTO_INCREMENT, `groupid` int(11) DEFAULT NULL, PRIMARY KEY (`studentid`) ) ENGINE=InnoDB AUTO_INCREMENT=66820 DEFAULT CHARSET=latin1 CREATE TABLE `iars` ( `id` int(11) NOT NULL AUTO_INCREMENT, `paperid` int(11) DEFAULT NULL, `groupid` int(11) DEFAULT NULL, `courseid` int(11) DEFAULT NULL, `semester` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=66820 DEFAULT CHARSET=latin1和这样的数据:
insert into students values (1,1,'a',1,1); insert into students values (2,1,'b',1,1); insert into iars values(1,1,1,1,1); insert into sgm values (1,1); insert into sps values (1,1);I'll try I guess:
select students.rollno, students.name from iars, students join sps on students.studentid = sps.studentid join sgm on students.studentid = sgm.studentid where iars.id = 1 and sps.paperid=iars.paperid and sgm.groupid=iars.groupid and students.course = iars.courseid and students.semester = iars.semesterAssuming tables like this:
CREATE TABLE `students` ( `studentid` int(11) NOT NULL AUTO_INCREMENT, `rollno` int(11) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `course` int(11) DEFAULT NULL, `semester` int(11) DEFAULT NULL, PRIMARY KEY (`studentid`) ) ENGINE=InnoDB AUTO_INCREMENT=66820 DEFAULT CHARSET=latin1 CREATE TABLE `sps` ( `studentid` int(11) NOT NULL AUTO_INCREMENT, `paperid` int(11) DEFAULT NULL, PRIMARY KEY (`studentid`) ) ENGINE=InnoDB AUTO_INCREMENT=66820 DEFAULT CHARSET=latin1 CREATE TABLE `sgm` ( `studentid` int(11) NOT NULL AUTO_INCREMENT, `groupid` int(11) DEFAULT NULL, PRIMARY KEY (`studentid`) ) ENGINE=InnoDB AUTO_INCREMENT=66820 DEFAULT CHARSET=latin1 CREATE TABLE `iars` ( `id` int(11) NOT NULL AUTO_INCREMENT, `paperid` int(11) DEFAULT NULL, `groupid` int(11) DEFAULT NULL, `courseid` int(11) DEFAULT NULL, `semester` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=66820 DEFAULT CHARSET=latin1And data like this:
insert into students values (1,1,'a',1,1); insert into students values (2,1,'b',1,1); insert into iars values(1,1,1,1,1); insert into sgm values (1,1); insert into sps values (1,1);更多推荐
发布评论