如何获取MAX数据，其中最大ID

问题描述：

我想用一个distinct电子邮件获取一些数据。如何获取MAX数据，其中最大ID

我的代码如下：

SELECT DISTINCT Email, 
       Degree, 
       Majority 
FROM job_education a 
WHERE a.Status = 'Active' 
AND a.IdEducationLevel = (
          SELECT Max(b.IdEducationLevel) 
          FROM job_education b 
          WHERE b.Status='Active' 
          AND a.Email = b.Email 
         )

由于之前

如果您可以提供一些样品数据和预期产量，那将是非常好的 – Hatik

您在'想要'的时候失去了我。 – Strawberry

你的代码有什么问题？ –

答

您的代码应该没问题，但我会修复表别名：

SELECT je.* 
FROM job_education je 
WHERE je.Status = 'Active' AND 
     je.IdEducationLevel = (SELECT MAX(je2.IdEducationLevel) 
          FROM job_education je2 
          WHERE je2.Status = 'Active' AND je2.Email = je.Email 
          );

如果你正在为给定的电子邮件多行 - 你只需要一个行 - 然后在子查询中使用更好ID：

SELECT je.* 
FROM job_education je 
WHERE je.Status = 'Active' AND 
     je.job_education_id = (SELECT je2.job_education_id 
          FROM job_education je2 
          WHERE je2.Status = 'Active' AND je2.Email = je.Email 
          ORDER BY IdEducationLevel DESC, job_education_id DESC 
          LIMIT 1 
          );

我发明了被称为job_education_id的ID，此目的。请注意，此查询使用ORDER BY和LIMIT而不是聚合函数。

答

你应该使用一个连接
你的好意（子选择不能看到上面参考表一）

SELECT DISTINCT Email, 
      Degree, 
      Majority 
    FROM job_education a 
    INNER JOIN (
     SELECT b.Email, Max(b.IdEducationLevel) max_IdEducationLevel 
     FROM job_education b 
     WHERE b.Status='Active' 
     GROUP BY b.Email 

    ) t ON a.Email = t.Email and a.IdEducationLevel = t.maxy_IdEducationLevel 
    WHERE a.Status = 'Active'

如何获取MAX数据，其中最大ID

相关推荐