mysql - 连接表中最后插入的数据

Question

我使用 zend-db 有以下查询。

$select = $db->select()->from('assignment',array('assignment.id','form_type','assignment.no_of_files'))
        ->join('assignment_insurance','assignment_insurance.assignment_id = assignment.id',array('assignment_insurance.assignment_id','insurance_company_id','insurance_claim_number'))
        ->join('insurance','insurance.id = assignment_insurance.insurance_company_id',array('company'))
        ->join('assignment_insured_or_claimant','assignment_insured_or_claimant.assignment_id = assignment.id',array('first_name','last_name'))
        ->join('assignment_appraiser','assignment_appraiser.assignment_id = assignment.id',array('appraiser_id','next_status_date'))
        ->join('appraiser','appraiser.id = assignment_appraiser.appraiser_id',array('first_name as appraiser_first_name','last_name as appraiser_last_name'))

这个查询工作正常，但我想加入从名为 status 的表中插入的最后一个数据。我想要加入的状态表的查询类似于，

SELECT * FROM `assignment_status` 
WHERE id=(the_assignment_id) ORDER BY id DESC LIMIT 1 

我该怎么做？请帮忙。谢谢

Answer 1

没关系，我已经解决了。

->joinInner('assignment_status', 'assignment_status.assignment_id = assignment.id AND assignment_status.id = (SELECT id from assignment_status where assignment_id = assignment.id ORDER BY id DESC LIMIT 1)',array('status','status_added_date','status_action'))