用于Mysql的Select sql语句

Question

我有一段sql

SELECT pd.id, pd.title, pd.fname, pd.lname, pd.DOB, pd.phone_home, pd.phone_biz, pd.phone_contact, pd.phone_cell, pd.sex, pd.email, pd.pid, f.form_id, ldf1.field_value AS autoleaxis, ldf2.field_value AS autolecyl
FROM patient_data pd
LEFT JOIN forms f ON pd.pid = f.pid
LEFT JOIN  `lbf_data` ldf1 ON ldf1.form_id = f.form_id
LEFT JOIN  `lbf_data` ldf2 ON ldf1.form_id = ldf2.form_id
WHERE (
f.form_name =  'Opthalmology'
AND ldf1.field_id =  'autoleaxis'
AND ldf2.field_id =  'autolecyl'
)
OR (
f.form_id IS NULL 
AND ldf1.field_id IS NULL 
AND ldf2.field_id IS NULL
)
ORDER BY pd.pid

这是针对MySQL数据库的。它工作得很好，除了在某些情况下，它会为每个人返回一个以上的记录，因为他们有多个form_id。如何限制它，使其只返回此人拥有的最高form_id的记录，如果没有，则将form_id设置为null。谢谢

Answer 1

您可以在LEFT JOIN中使用子查询，如下所示：

SELECT *
FROM patient_data pd
LEFT JOIN 
(
  select max(form_id) as form_id, pid, form_name
  from forms as f2
) as f
ON pd.pid = f.pid
LEFT JOIN  `lbf_data` ldf1 ON ldf1.form_id = f.form_id
LEFT JOIN  `lbf_data` ldf2 ON ldf1.form_id = ldf2.form_id
WHERE (
f.form_name =  'Opthalmology'
AND ldf1.field_id =  'autoleaxis'
AND ldf2.field_id =  'autolecyl'
)
OR (
f.form_id IS NULL 
AND ldf1.field_id IS NULL 
AND ldf2.field_id IS NULL
)
ORDER BY pd.pid

您可以在here中进行测试