代码之家 › 专栏 › 技术社区 › Ross

有没有可能把这些查询浓缩成一个查询?

mysql

Ross · 技术社区 · 14 年前

我有两个表,其中一个表包含引用另一个表的记录:

Goal
  id (int)
  name (text)
  value_mask (text)

GoalStatus
  id (int)
  goal (int)
  created (datetime)
  value (text)

Goal.id == GoalStatus.goal

我想做的是,从GoalStatus表中为Goal中的每条记录提取最新的记录。目前我知道的唯一方法是对Goal中的每条记录进行单独的查询(伪代码):

goals = db.query("SELECT * FROM Goal")

foreach (goals as goal):
    goalStatus = db.query("
        SELECT * FROM GoalStatus
        WHERE goal = " + goal.id + "
        ORDER BY created DESC
        LIMIT 1
    ")

3 回复 | 直到 14 年前

bobince 14 年前

Here's a summary

SELECT ...
FROM Goal
JOIN GoalStatus AS Gs0 ON Gs0.goal=Goal.id
LEFT JOIN GoalStatus AS Gs1 ON Gs1.goal=Goal.id AND Gs1.created>Gs0.created
WHERE Goal.id=(someid)
AND Gs1.id IS NULL

也就是说,在没有其他行具有更大的 created 价值观。

DRapp 14 年前

select 
      g.*,
      gs.id GoalStatusID,
      gs.created,
      gs.value
   from 
      goal g inner join goalstatus gs
          on g.id = gs.goal
   where 
      gs.created in 
          ( select max( gs2.created )
               from goalstatus gs2
               where g.id = gs2.goal )

tQuarella 14 年前

SELECT * 
FROM Goal 
    INNER JOIN GoalStatus on Goal.GoalId=GoalStatus.GoalId 
WHERE Not Exists(SELECT * 
                 FROM GoalStatus innerGs 
                 WHERE innerGs.GoalId=Goal.GoalId 
                     and innerGs.created > GoalStatus.created)