24小时热门版块排行榜

>论坛更新日志 (3568)
>文献求助 (355)
>导师招生 (288)
>虫友互识 (268)
>考博 (150)
>休闲灌水 (125)
>招聘信息布告栏 (98)
>硕博家园 (96)
>博后之家 (82)
>基金申请 (60)
>论文投稿 (57)
>考研 (50)
>论文道贺祈福 (49)
>公派出国 (43)
>找工作 (38)
>教师之家 (33)

返回列表

2012200838

新虫 (正式写手)

应助: 0 (幼儿园)
金币: 3126.7
红花: 3
帖子: 313
在线: 69.5小时
虫号: 3122844
注册: 2014-04-09
性别: GG
专业: 计算机软件

[求助] MySQL多表查询问题（查询结果有重复）

需求：
需求：
设计数据库表存储：（用户考试信息）
用户信息、考试时间、考试科目与考试成绩，及所属年级！
2. 测试数据：
U001, 张三，1985-09-09, 广州天河,
java,80,基础班,  考试时间为2014-01-01
jsp,90,就业班,  考试时间为2014-03-01
mysql,90, 就业班, 考试时间为2014-04-04
U002 ,李四，1995-09-09, 广州越秀,
java,67,基础班,  考试时间为2014-01-01
mysql,90, 就业班, 考试时间为2014-04-04
……….(录入其他记录)

  提示：最好是四个表（使用约束）
3. 查询需求：
 查询学号是U001的学生参加2014-01-01 “java”课程考试的成绩，要求输出学生姓名和成绩
 查询出通过考试（高于60分）的学员所在的姓名、、所属学学习阶段、考试科目名称、学员的成绩。

 利用子查询语句，筛选出生日期比“李四”大的学生
 查询“java”课程考试成绩为60-80分的学生名单
 查询参加最近一次“mysql”考试成绩最高分和最低分
 查询出基础班考试的平均成绩；
 需求（存储过程）
- 统计并显示2014-04-04的mysql考试平均分
- 如果平均分在70以上，显示“考试成绩优秀”
- 如果在70以下，显示“考试成绩较差”

------------------------------------------------------------------------------------------------------------------------------------------------------------------
我做了三个表，一个考生表candidateInfo，存储考生自然信息，一个科目表subjects,存储科目信息。一个中间表，存储考生学习的所有科目和相应科目的考试时间，考试成绩。

ccreate database candidatedb

use candidatedb;

drop database candidate;

-- --------------- table: candidateInfo ------------------
create table candidateInfo(
id varchar(5) primary key,
name varchar(20),
birthday date,
address varchar(100)
);

show create table candidateInfo;

desc candidateInfo;

insert into candidateInfo values('U001','张三','1988/01/15','陕西西安');
insert into candidateInfo values('U002','李四','19880515','陕西渭南');
insert into candidateInfo values('U003','王五','1988-05-15','陕西长安');
insert into candidateInfo values('U004','韩六','1989-06-04','陕西铜川');

select * from candidateInfo;

-- ---------------table: subjects ---------------
create table subjects(
id int primary key,
name varchar(20),
class varchar(50)
);

desc subjects;

insert into subjects values(1,'java','BeginnerClass');
insert into subjects values(2,'JSP','JobClass');
insert into subjects values(3,'MySQL','JobClass');

select * from subjects;

-- ---------------table: intermediateTable---------------
create table intermediateTable(
candidateID varchar(5),
subjectID int,
TestDate date,
Score double,
constraint foreignKey1 foreign key(candidateID) references candidateInfo(id),
constraint foreignKey2 foreign key(subjectID) references subjects(id)
);

desc intermediateTable;

insert into intermediateTable values('U001',1,'2014-01-01',80);
insert into intermediateTable values('U001',2,'2014-01-02',90);
insert into intermediateTable values('U001',3,'2014-01-03',58);

insert into intermediateTable values('U002',1,'2014-09-01',80);
insert into intermediateTable values('U002',3,'2014-09-03',78);

insert into intermediateTable values('U003',1,'2015-01-01',60);
insert into intermediateTable values('U003',2,'2015-01-02',56);
insert into intermediateTable values('U003',3,'2015-01-03',30);

insert into intermediateTable values('U004',1,'2016-01-01',100);
insert into intermediateTable values('U004',2,'2016-01-02',90);
insert into intermediateTable values('U004',3,'2016-01-03',88);

select * from intermediateTable;

但是在查询时有问题：
比如第一个需求：
查询学号是U001的学生参加2014-01-01 “java”课程考试的成绩，要求输出学生姓名和成绩

因为一个考生及其考试科目确定，那么这个考生这个科目的考试时间和成绩是确定的。所以我的代码这么写的，但是有问题：

CODE:

select c.name,s.name,i.score,i.TestDate         from candidateInfo as c,subjects as s,intermediateTable as i         where c.id='U001' and s.name='java'

查询结果如下：
name name score TestDate
张三 java 80 2014-01-01
张三 java 90 2014-01-02
张三 java 58 2014-01-03
张三 java 80 2014-09-01
张三 java 78 2014-09-03
张三 java 60 2015-01-01
张三 java 56 2015-01-02
张三 java 30 2015-01-03
张三 java 100 2016-01-01
张三 java 90 2016-01-02
张三 java 88 2016-01-03

明显不对，我加上了日期字段：

CODE:

select c.name,s.name,i.score,i.TestDate         from candidateInfo as c,subjects as s,intermediateTable as i         where c.id='U001' and s.name='java' and i.TestDate='2014-01-01'

name name score TestDate
张三 java 80 2014-01-01
但是还是有问题，我把日期换成其他科目的，还是有显示,但是结果是错误的。

问题出在了哪里？

回复此楼

» 猜你喜欢

生命就是一场旅行，不要错过了路边的风景！

1楼 2016-06-19 09:21:18

已阅回复此楼关注TA 给TA发消息送TA红花 TA的回帖

相关版块跳转我要订阅楼主 2012200838 的主题更新

返回列表