mysql - 加入返回不正确的值

Question

数据库的表结构:
教练表

+----+-----------+-------+  
| ID |   Name    | Age   |  
+----+-----------+-------+  
|  1 | Aaron     |  39   |  
|  2 | John      |  41   |  
|  3 | Macy      |  44   |  
|  4 | Mitchelle |  37   |  
|  5 | Candice   |  32   |  
+----+-----------+-------+  

学员表

+----+---------+-------+  
| ID |  Name   | Age   |  
+----+---------+-------+  
|  6 | Abigail |   9   |  
|  7 | Jim     |  12   |  
|  8 | Jack    |   7   |  
|  9 | Maria   |  14   |  
| 10 | Andy    |   11  |  
+----+---------+-------+  

费用表

+----+----------+------------+--------+  
| ID | Coach_ID | Trainee_ID | Fee    |  
+----+----------+------------+--------+  
| 11 |        1 |          7 | 2400   |  
| 12 |        2 |          6 | 2000   |  
| 13 |        3 |          6 | 2000   |  
| 14 |        4 |          8 | 1243   |  
| 15 |        5 |          8 | 1275   |  
| 16 |        3 |          9 | 9010   |  
| 17 |        2 |          8 | 1900   |  
| 18 |        1 |          7 |  600   |  
| 19 |        2 |         10 | 1010   |  
| 20 |        5 |         10 |   2110 |  
+----+----------+------------+--------+  

要求的输出

+-----------+--------------+--------+  
|   Name    | Trainee_name | Fee    |  
+-----------+--------------+--------+  
| Aaron     | Jim          | 2400   |  
| Candice   | Andy         | 2110   |  
| John      | Abigail      | 2000   |  
| Macy      | Maria        | 9010   |  
| Mitchelle | Jack         |  1243  |  
+-----------+--------------+--------+  

=====

我的查询

Select 
 Coach.Name as Name, Trainee.Name as Trainee_name, Max(Fee.Fee) as Fee 
From Fee
 INNER JOIN Trainee
  ON Fee.Trainee_ID = Trainee.ID
 INNER JOIN Coach
  ON Fee.Coach_ID = Coach.ID 
GROUP BY Coach.Name
ORDER BY Coach.Name; 

我的输出:

+------------+---------------+--------+  
|   Name     | Trainee_name  | Fee    |  
+------------+---------------+--------+  
| Aaron      | Jim           | 2400   |  
| Candice    | Jack          | 2110   |  
| John       | Abigail       | 2000   |  
| Macy       | Abigail       | 9010   |  
| Mitchelle  | Jack          |  1243  |  
+------------+---------------+--------+ 

trainee_name栏中Candice和Macy对应的数据不正确。

sql数据库转储:

CREATE TABLE IF NOT EXISTS `coach` (  
  `ID` int(11) NOT NULL,  
  `Name` text NOT NULL,  
  `Age` int(11) NOT NULL  
) ENGINE=InnoDB DEFAULT CHARSET=latin1;  


INSERT INTO `coach` (`ID`, `Name`, `Age`) VALUES  
(1, 'Aaron', 39),  
(2, 'John', 41),  
(3, 'Macy', 44),  
(4, 'Mitchelle', 37),  
(5, 'Candice', 32);  


CREATE TABLE IF NOT EXISTS `fee` (  
  `ID` int(11) NOT NULL,  
  `Coach_ID` int(11) NOT NULL,  
  `Trainee_ID` int(11) NOT NULL,  
  `Fee` int(11) NOT NULL  
) ENGINE=InnoDB DEFAULT CHARSET=latin1;  


INSERT INTO `fee` (`ID`, `Coach_ID`, `Trainee_ID`, `Fee`) VALUES  
(11, 1, 7, 2400),  
(12, 2, 6, 2000),  
(13, 3, 6, 2000),  
(14, 4, 8, 1243),  
(15, 5, 8, 1275),  
(16, 3, 9, 9010),  
(17, 2, 8, 1900),  
(18, 1, 7, 600),  
(19, 2, 10, 1010),  
(20, 5, 10, 2110);  

CREATE TABLE IF NOT EXISTS `trainee` (  
  `ID` int(11) NOT NULL,  
  `Name` text NOT NULL,  
  `Age` int(11) NOT NULL  
) ENGINE=InnoDB DEFAULT CHARSET=latin1;  


INSERT INTO `trainee` (`ID`, `Name`, `Age`) VALUES  
(6, 'Abigail', 9),  
(7, 'Jim', 12),  
(8, 'Jack', 7),  
(9, 'Maria', 14),  
(10, 'Andy', 11);  

Answer 1

试试这个:

SELECT sub.name, trainee.name, sub.mx
FROM
(SELECT Fee.Coach_ID, Coach.Name, MAX(fee) AS mx
 FROM Coach
 INNER JOIN Fee ON Coach.ID = Fee.Coach_ID
 GROUP BY Fee.Coach_ID, Coach.Name) sub
INNER JOIN fee ON sub.coach_ID = fee.coach_ID
INNER JOIN trainee ON fee.trainee_ID = trainee.id
WHERE sub.mx = fee.fee
ORDER BY sub.name

你可以看到我在这里运行的测试:http://sqlfiddle.com/#!9/cdbad/10 .

它有你想要的输出。诀窍是子查询。您需要首先确定每位教练的最高费用。然后通过费用表加入学员。