php - 如何使用 MYSQL 将 Max 函数与组一起使用

Question

我有那 3 张 table

候选人

cdid   cdname   1      Moussa2      Moustafa3      Haitham

positions

posid  posname   1      pos12      pos23      pos3

joboffers

cdid    posid   salary1        1       6001        2       8001        3       7002        1       5002        2       7002        3       9003        2       10003        3       500

I need to make a query that get the cdname with max salary for every position

the table should be like this

cdname         posname       salarymoussa          pos1          600haitham         pos2          1000moustafa        pos3          900

I am using this query but it's not getting the correct cdid

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

            Group by joboffers.posid"; 

Answer 1

试试这个:)

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