MySQL查询最新的重复项

Question

编辑:

我正在尝试执行一个 MySQL 查询，它将为我提供最新的重复项以及没有任何重复项的条目。

这是我的 table 的样子:

导师表:

   mentor_id    applicant_id    mentor_count    mento_email mentor_name mentor_pass
    193     92      1       test@yahoo.com  test        1234
    194     92      2       table@yahoo.com table       4567
    195     92      3       lamp@yahoo.com  lamp        7890
    196     92      1       test@yahoo.com  test        1234
    197     92      2       table@yahoo.com table       4567
    198     92      3       lamp@yahoo.com  lamp        7890

mf表:

  mf_id     mentor_id   dept        contact     orgname     yrs     length      sak social  char    goal    resp    emomat  res others  impact  evaluation
43      193     math dept   9111111     etc     1       1       e   e   e   e   e   e   e   e   e   good
114     196     math dept   9111111     etc     1       1       e   e   e   e   e   e   e   e   e   good
193     197     sci dept    9222222     org     2       2       n   n   n   n   n   n   n   n   n   medium
194     194     sci dept    9222222     org     2       2       n   n   n   n   n   n   n   n   n   medium
220     195     eng dept    9333333     hello       3       3       q   q   q   q   q   q   q   q   q   bad

我尝试使用这个查询:

 SELECT *
FROM mentor m1
LEFT JOIN (

SELECT mentor_name, max( mentor_id ) AS maxid
FROM mentor m
GROUP BY m.mentor_id
)m2 ON m1.mentor_name = m2.mentor_name
AND m1.mentor_id < m2.maxid
LEFT JOIN mf ON m1.mentor_id = mf.mentor_id
WHERE m1.applicant_id =833
AND m2.maxid IS NULL
ORDER BY m1.mentor_id ASC
LIMIT 0 , 30 

但这是发生了什么:

mentor_id   applicant_id    mentor_count    mentor_email    mentor_name mentor_pass mentor_name maxid   mf_id   mentor_id   dept    contact orgname yrs length  sak social  char    goal    resp    emomat  res others  spirit  concept comm    impact  evaluation
/*there is data here but the column for mentor_name onwards is null*/

我怎样才能使 mentor_name 之后的列不为空，但仍然显示最新的重复项以及没有任何重复项的列？

Answer 1

尝试

select * from mentor 
where mentor_id in 
(
      SELECT max(mentor_id) from mf 
      where applicant_id = 92 
      group by mentor_id
)