我有一个包含学生姓名的表格,自动增加NR
记录,但是在每次编辑记录时,都会创建一个新记录,将NR
复制到ID
字段。但是当我尝试在 MAX( NR
) 时对 ID 记录进行分组时,它向我显示了该 ID 的最大数量,但是当我要求剩余的 rocord 时,它没有向我显示该组 ID 的最后一条记录
SELECT MAX(`NR`) AS 'mNr',`NR`,`ID`,`Name1`,`Name3`,`Gender`
FROM `Kids` GROUP BY `ID`
这会产生如下结果:
mNr NR ID 名称1 名称3 性别252 1 1 爱丽丝·卡珀 f179 2 2 多拉·费舍尔 f189 3 3 拉切尔·金 f173 4 4 弗兰克·史密斯 m192 5 5 帕特里克·费伊 m305 6 6 格洛丽亚·辛 f299 7 7 布里奇特·杨 f但
如您所见,查询显示最高的编辑 NR,但随后继续提供记录其余部分的最低,而不是属于该最新 NR 的记录详细信息......我做错了什么?以下是示例数据:
NR ID Name1 Name3 Gender
1 1 Alice Achand f
2 2 Dorah Achieng f
3 3 Racheal Achieng f
4 4 Francisca Adikin f
5 5 Patrick Adilu m
6 6 Gloria Ajwang f
7 7 Bridget Aketch f
130 5 Patrick Adilu m
129 4 Francisca Adikin f
128 2 Dorah Achieng f
153 4 Francisca Adikin f
173 4 Francisca Adikin f
179 2 Dorah Achieng f
189 3 Racheal Achieng f
192 5 Patrick Adilu m
252 1 Alice Wor f
299 7 Bridget Aketch f
305 6 Gloria Ajwang f
也许在不知情的情况下,您正在使用MySQL的神秘功能。 MySQL 允许您在聚合查询的 select
语句中包含不在聚合函数或 group by
子句中的列。 引擎为这些列输入任意值。
执行所需操作的正确方法是使用联接:
SELECT k.*
FROM `Kids` k join
(select id, max(nr) as maxnr
from kids
group by id
) m
on k.id = m.id and nr = maxnr;
以下是文档中的明确说明:
MySQL扩展了GROUP BY的使用,以便选择列表可以引用 未在 GROUP BY 子句中命名的非聚合列。这意味着 上述查询在 MySQL 中是合法的。您可以使用此功能 通过避免不必要的列排序和 分组。但是,这主要在 每个值中的所有值时有用 未在 GROUP BY 中命名的非聚合列对于每个列都是相同的 群。服务器可以自由地从每个组中选择任何值,因此 除非它们相同,否则选择的值是不确定的。 此外,从每个组中选择的值不能 受添加 ORDER BY 子句的影响。结果集的排序 在选择值后发生,并且 ORDER BY 不影响 服务器在每个组中选择哪些值。
您可以在此处更详细地阅读。
尽管@Gordon Linoff的答案在技术上是正确的,但在大型数据集上使用子查询可能会花费更多的资源和时间。
根据具体情况,我通常会将数据分成两个表,students
和student_details
students
的表结构为
CREATE TABLE students (
student_id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
);
此表的想法是为学生创建一个唯一的编号,并存储您可能不希望保存在修订中的任何其他学生数据。
student_details
的表结构为:
CREATE TABLE student_details (
revision_id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
student_id INTEGER NOT NULL,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
gender VARCHAR(1),
is_history BOOLEAN NOT NULL DEFAULT false,
CONSTRAINT FOREIGN KEY(student_id) REFERENCES students(student_id) ON DELETE RESTRICT
);
此表存储了学生的实际数据。更新某个学生的数据时,您只需将is_history
列更新为true
该学生的记录。然后,在选择学生数据时,您只需使用 SELECT student_details.* FROM students LEFT JOIN student_details ON (student_details.student_id = students.student_id AND student_details.is_history = false)
.这将始终返回学生详细信息的最新修订版。
插入新学生
- 插入学生
INSERT INTO students(student_id) VALUES('');
- 获取最后一个插入 ID
SELECT LAST_INSERT_ID();
(假设在本例中它返回 1) - 插入学生详细信息
INSERT INTO student_details(student_id, first_name, last_name, gender) VALUES('1', 'Alice', 'Carper', 'f')
更新现有学生(假设 student_id = 1)
- 将所有以前的student_detail"修订"设置为"历史记录":
UPDATE student_details SET is_history = true WHERE student_details.student_id = 1 AND is_history = false
- 添加新修订版本:
INSERT INTO student_details(student_id, first_name, last_name, gender) VALUES('1', 'Alice', 'Achand', 'f')
获取学生及其最新学生详细信息
SELECT student_details.* FROM students LEFT JOIN student_details ON (student_details.student_id = students.student_id AND student_details.is_history = false)