如何使用MYSQL将Max函数与组一起使用 - How To Use Max function with group by using MYSQL

How To Use Max function with group by using MYSQL

本文关键字：一起函数何使用 MYSQL Max | 更新日期: 2023-09-27

我有这3个表

候选

cdid cdname1只穆萨2穆斯塔法3 Haitham

仓位

posid posname1个pos12 pos23 pos3

工作机会

cdid职位工资1 160012001 3 70025002 2 7002 3 9003 2 10003 3 500

我需要查询每个职位的最高工资的cdname

桌子应该像这个

cdname posname薪水摩沙pos1 600海地pos2 1000穆斯塔法pos3 900

我正在使用此查询，但它没有得到正确的cdid

$sql="select joboffers.cdid,joboffers.posid,Max(joboffers.salary),candidates.cdname
            FROM joboffers,candidates
            Where joboffers.cdid=candidates.cdid 
            Group by joboffers.posid";

试试这个：）

SELECT sub.cdname, sub.posname, sub.salary
FROM (
  SELECT * FROM joboffers jo
  INNER JOIN positions ps USING (posid)
  INNER JOIN candidates cd USING (cdid)
  ORDER BY posid, salary DESC
) sub
GROUP BY sub.posid

我认为您需要按所有非聚合列进行分组，如下所示：

SELECT j.cdid, c.cdname, j.posid, MAX(j.salary)
FROM joboffers j
JOIN candidates c ON j.cdid = c.cdid
GROUP BY j.cdid, c.cdname, j.posid

 SELECT c.cdname, p.posname, MAX(j.salary)
 FROM joboffers j
 INNER JOIN candidates c ON j.cdid = c.cdid
 INNER JOIN positions p ON j.posid = p.posid
 GROUP BY c.cdname, p.posname

enter code here SELECT customerNumber，MAX（金额）FROM付款GROUP BY客户编号最高订单（金额）；