微商城网站开发百度品牌推广
我正在进行sql选择,我需要获取ID的所有最小记录.我的问题是,虽然我认为我的代码工作正常,但它通常会返回其他列的错误值,显然是因为我没有使用必须支持我的聚合min函数.这是我的代码.
SELECT *
FROM example_students
INNER JOIN
(SELECT id, student, test, subject, MIN(score), semester
FROM example_student_scores
WHERE studentid=94
GROUP BY studentid, test, subject) as scores
ON example_students.id=scores.studentid
这就是我希望我的代码所做的事情.
select from exampletable the minimum score for each distinct test and subject combination where the student has id#94
以下是该示例的两个(经过大量修改的)表格(我在此处和我的代码中也更改了所有列名称.
example_students
id name
----+-----------+
94 Bob
1023 Thomas
.
example_students_scores
id studentId test subject score semester
----+-----------+-----------+-----------+-----------+-----------
0 94 quiz math 46 fall
1 94 quiz math 71 fall
2 94 quiz math 63 winter
3 94 midterm math 94 winter
4 94 midterm science 72 fall
5 94 quiz math 50 spring
6 94 final math 76 spring
7 1023 quiz math 6 spring
8 1023 quiz math 52 winter
9 1023 quiz science 68 fall
..*
结果应该是
results
id studentId test subject score semester
----+-----------+-----------+-----------+-----------+-----------
0 94 quiz math 46 fall
3 94 midterm math 94 winter
4 94 midterm science 72 fall
6 94 final math 76 spring
问题是,我将得到学期列的错误值(以及我在实际工作中的所有其他列).
鉴于这需要我很长时间才能得到无处,这里是sql来创建两个示例数据库：
drop table if exists example_students;
drop table if exists example_students_scores;
create table example_students(
id int(10) primary key,
name char(25)
);
create table example_students_scores(
id int(10) not null,
studentId int(10) not null,
test char(20),
subject char(20),
score int(10) not null default '0',
semester char(20),
primary key (id),
index studentid (studentid)
);
insert into example_students values ('94','Bob');
insert into example_students values ('1023','Thomas');
insert into example_students_scores values ('0' ,'94' ,'quiz' ,'math' ,'46' ,'fall');
insert into example_students_scores values ('1' ,'94' ,'quiz' ,'math' ,'71' ,'fall');
insert into example_students_scores values ('2' ,'94' ,'quiz' ,'math' ,'63' ,'winter');
insert into example_students_scores values ('3' ,'94' ,'midterm' ,'math' ,'94' ,'winter');
insert into example_students_scores values ('4' ,'94' ,'midterm' ,'science' ,'72' ,'fall');
insert into example_students_scores values ('5' ,'94' ,'quiz' ,'math' ,'50' ,'spring');
insert into example_students_scores values ('6' ,'94' ,'final' ,'math' ,'76' ,'spring');
insert into example_students_scores values ('7' ,'1023' ,'quiz' ,'math' ,'6' ,'spring');
我会感激任何指示或提示,只是在上线一周后才弄清楚你的工作是错误的,这是非常尴尬的！