我应该在表格中创建一个新字段还是只选择第二个表格的最大值 [英] Should I create a new field in the table or just select the MAX of the second table

问题描述

关于这个问题，我收到了一个效果很好的答案.我现在想知道是否有可能有更好的结构.

On this question I received an answer that worked well. I am now wondering if there is a possible better structure.

我有两个桌子.

Projects : id, title
Status : project_id, status_id, created(DATETIME)

此刻，我要获取项目的状态，并获取项目ID，然后根据项目ID将最新的行从状态表中拉出.要获得最新的行是很麻烦的.

At the moment to get the status of my project, I get the project ID and pull the latest row out of the status table based on the project id. To get this latest row is quite a hassle.

我是否应该将模式更改为此?

Should I rather change the schema to this?

Projects : id, title, current_status_id(FK)
Status : id(PK), project_id, status_id, created(DATETIME)

然后我可以将表格与FK联接在一起，而无需查找最新行就可以得到我想要的行?

Then I can just join the tables with the FK and get the row that I want without looking for the latest?

编辑:

所以我想要这样的东西

SELECT * FROM projects
LEFT JOIN status on projects.id = status.project_id
WHERE projects.id = 1

但是我只想要状态表中的最新记录.

But I want only the latest record in the status table.

编辑2 :

所以我想要这样的东西

SELECT * FROM projects
LEFT JOIN status on projects.id = status.project_id

但是对于返回的每个项目，只能从状态中获取该project_id的最新状态记录.

But for each project returned, only get the latest status record for that project_id from status.

推荐答案

这麻烦吗?

SELECT project_id, status_id, created 
FROM Status 
WHERE project_id = the-id
ORDER BY created DESC
LIMIT 1;

或者，如果您需要多个项目的列表:

Or, if you need a list of multiple projects:

SELECT a.project_id, a.status_id, a.created 
FROM Status a
LEFT JOIN Status b
ON a.project_id = b.project_id
AND b.created > a.created
WHERE a.project_id IN(id1, id2, id3) AND b.project_id IS NULL;

因此，使用项目数据:

SELECT Projects.*, Status.*
FROM Projects
LEFT JOIN Status 
ON Status.project_id = Projects.id
WHERE Projects.id = the-id
ORDER BY Status.created DESC
LIMIT 1;

或者:

SELECT Projects.*, Status.*
FROM Projects
LEFT JOIN Status a
ON a.project_id = Projects.id
LEFT JOIN Status b
ON a.project_id = b.project_id
AND b.created > a.created
WHERE b.project_id IS NULL;

这篇关于我应该在表格中创建一个新字段还是只选择第二个表格的最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！