mysql最少_mysql – 选择最少两个不同的值

我正在进行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');

我会感激任何指示或提示,只是在上线一周后才弄清楚你的工作是错误的,这是非常尴尬的！