mysql - 获取选择多个表的最新行

Question

我有这个查询:

SELECT DISTINCT 
    enc.id, enc.cus_id, enc.createdon, enc.status, enc.segment, enc_task.orderid, 
    enc_task.taskid, enc.currentstep, enc.groupid, enc.fdprotocol, 
    enc_task.linkfile, cus.fname, cus.lname, login.first_name, login.last_name, 
    login.username, login.user_code, login.id as assigned_id, fp.protocol, 
    init.typename, table4.allowtotal 
FROM 
    mob_encounters enc 
    JOIN mob_encounters_task enc_task ON enc_task.encounterid=enc.id
    JOIN mob_customer cus ON cus.id=enc_task.cus_id
    JOIN mob_login login ON login.id=enc.createdby
    LEFT JOIN mob_protocol_type fp ON fp.id=enc.fdprotocol
    LEFT JOIN initiation_type init ON init.id=enc.groupid
    LEFT JOIN mob_table4 table4 ON table4.encid=enc.id

GROUP BY 
    enc.id

查询正在运行，但在进行此匹配时我需要它返回 enc_task.encounterid 的最新行:enc_task.encounterid = enc.id。可能吗？

Answer 1

子查询可能会满足您的要求:

SELECT DISTINCT 
                enc.id, enc.cus_id, enc.createdon, enc.status, enc.segment, enc_task.orderid, enc_task.taskid,
                enc.currentstep, enc.groupid, enc.fdprotocol, enc_task.linkfile, cus.fname, 
                cus.lname, login.first_name, login.last_name, login.username, login.user_code,
                login.id as assigned_id, fp.protocol, init.typename, table4.allowtotal 
            FROM 
                mob_encounters enc 
            JOIN 
                (select encounterid, orderid, taskid, linkfile from mob_encounters_task order by [FIELD THAT DESIGNATES LATEST VALUE] desc) AS enc_task ON enc_task.encounterid=enc.id
            JOIN 
                mob_customer cus ON cus.id=enc_task.cus_id
            JOIN 
                mob_login login ON login.id=enc.createdby
            LEFT JOIN 
                mob_protocol_type fp ON fp.id=enc.fdprotocol
            LEFT JOIN 
                initiation_type init ON init.id=enc.groupid
            LEFT JOIN 
                mob_table4 table4 ON table4.encid=enc.id
            GROUP BY 
                enc.id