https://blog.csdn.net/weixin_43236925/article/details/146382981
清晰易懂的 PHP 安装与配置教程
12.6 查找每组行中含有最大或最小值的行
mysql> set @max_price = (select max(price) from painting);
Query OK, 0 rows affected (0.01 sec)mysql> select artist.name, painting.title, painting.price-> from artist inner join painting-> on painting.a_id = artist.a_id-> where painting.price = @max_price;
+----------+-----------+-------+
| name | title | price |
+----------+-----------+-------+
| Da Vinci | Mona Lisa | 87 |
+----------+-----------+-------+
1 row in set (0.00 sec)mysql> create table tmpp select max(price) as max_price from painting;
Query OK, 1 row affected (0.07 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> select artist.name, painting.title, painting.price-> from artist inner join painting inner join tmpp-> on painting.a_id = artist.a_id-> and painting.price = tmpp.max_price;
+----------+-----------+-------+
| name | title | price |
+----------+-----------+-------+
| Da Vinci | Mona Lisa | 87 |
+----------+-----------+-------+
1 row in set (0.00 sec)mysql> create table tmpp1-> select a_id, max(price) as max_price from painting group by a_id;
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql> select artist.name, painting.title, painting.price-> from artist inner join painting inner join tmpp1-> on painting.a_id = artist.a_id-> and painting.a_id = tmpp1.a_id-> and painting.price = tmpp1.max_price;
+----------+-------------------+-------+
| name | title | price |
+----------+-------------------+-------+
| Da Vinci | Mona Lisa | 87 |
| Van Gogh | The Potato Eaters | 67 |
| Renoir | Les Deux Soeurs | 64 |
+----------+-------------------+-------+
3 rows in set (0.00 sec)mysql> select artist.name, painting.title, painting.price-> from artist inner join painting inner join-> (select a_id, max(price) as max_price from painting group by a_id)-> as tmpp1-> on painting.a_id = artist.a_id-> and painting.a_id = tmpp1.a_id-> and painting.price = tmpp1.max_price;
+----------+-------------------+-------+
| name | title | price |
+----------+-------------------+-------+
| Da Vinci | Mona Lisa | 87 |
| Van Gogh | The Potato Eaters | 67 |
| Renoir | Les Deux Soeurs | 64 |
+----------+-------------------+-------+
3 rows in set (0.00 sec)mysql> select p1.a_id, p1.title, p1.price-> from painting as p1 left join painting as p2-> on p1.a_id = p2.a_id and p1.price < p2.price-> where p2.a_id is null;
+------+-------------------+-------+
| a_id | title | price |
+------+-------------------+-------+
| 1 | Mona Lisa | 87 |
| 3 | The Potato Eaters | 67 |
| 4 | Les Deux Soeurs | 64 |
+------+-------------------+-------+
3 rows in set (0.00 sec)mysql> select artist.name, p1.title, p1.price-> from painting as p1 left join painting as p2-> on p1.a_id = p2.a_id and p1.price < p2.price-> inner join artist on p1.a_id = artist.a_id-> where p2.a_id is null;
+----------+-------------------+-------+
| name | title | price |
+----------+-------------------+-------+
| Da Vinci | Mona Lisa | 87 |
| Van Gogh | The Potato Eaters | 67 |
| Renoir | Les Deux Soeurs | 64 |
+----------+-------------------+-------+
3 rows in set (0.00 sec)mysql> DROP TABLE IF EXISTS driver_log;
Query OK, 0 rows affected (0.05 sec)mysql> #@ _CREATE_TABLE_
mysql> CREATE TABLE driver_log-> (-> rec_id INT UNSIGNED NOT NULL AUTO_INCREMENT,-> name VARCHAR(20) NOT NULL,-> trav_date DATE NOT NULL,-> miles INT NOT NULL,-> PRIMARY KEY (rec_id)-> );
Query OK, 0 rows affected (0.05 sec)mysql> #@ _CREATE_TABLE_
mysql>
mysql> INSERT INTO driver_log (name,trav_date,miles)-> VALUES-> ('Ben','2006-08-30',152),-> ('Suzi','2006-08-29',391),-> ('Henry','2006-08-29',300),-> ('Henry','2006-08-27',96),-> ('Ben','2006-08-29',131),-> ('Henry','2006-08-26',115),-> ('Suzi','2006-09-02',502),-> ('Henry','2006-09-01',197),-> ('Ben','2006-09-02',79),-> ('Henry','2006-08-30',203)-> ;
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0mysql>
mysql> SELECT * FROM driver_log;
+--------+-------+------------+-------+
| rec_id | name | trav_date | miles |
+--------+-------+------------+-------+
| 1 | Ben | 2006-08-30 | 152 |
| 2 | Suzi | 2006-08-29 | 391 |
| 3 | Henry | 2006-08-29 | 300 |
| 4 | Henry | 2006-08-27 | 96 |
| 5 | Ben | 2006-08-29 | 131 |
| 6 | Henry | 2006-08-26 | 115 |
| 7 | Suzi | 2006-09-02 | 502 |
| 8 | Henry | 2006-09-01 | 197 |
| 9 | Ben | 2006-09-02 | 79 |
| 10 | Henry | 2006-08-30 | 203 |
+--------+-------+------------+-------+
10 rows in set (0.00 sec)mysql> select name, trav_date, miles-> from driver_log-> order by name, trav_date;
+-------+------------+-------+
| name | trav_date | miles |
+-------+------------+-------+
| Ben | 2006-08-29 | 131 |
| Ben | 2006-08-30 | 152 |
| Ben | 2006-09-02 | 79 |
| Henry | 2006-08-26 | 115 |
| Henry | 2006-08-27 | 96 |
| Henry | 2006-08-29 | 300 |
| Henry | 2006-08-30 | 203 |
| Henry | 2006-09-01 | 197 |
| Suzi | 2006-08-29 | 391 |
| Suzi | 2006-09-02 | 502 |
+-------+------------+-------+
10 rows in set (0.00 sec)mysql> create table tmpp2-> select name, max(trav_date) as trav_date-> from driver_log group by name;
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 01. 第一条语句：用外部表tmpp2进行内连接
sql
SELECT driver_log.name, driver_log.trav_date, driver_log.miles
FROM driver_log
INNER JOIN tmpp2 -- tmpp2是一个已存在的外部表ON driver_log.name = tmpp2.name AND driver_log.trav_date = tmpp2.trav_date
ORDER BY driver_log.name;• tmpp2的性质：这里的tmpp2是一个预先存在的外部表（可能是手动创建的临时表或永久表），里面存储了一些name和trav_date的数据。
• 连接逻辑：只返回driver_log中与tmpp2表中完全匹配（name和trav_date都相同）的记录。
• 适用场景：当你需要筛选driver_log中符合某个预设条件（即tmpp2表中定义的特定name和trav_date组合）的记录时使用。例如：tmpp2可能存储了 “需要重点检查的司机和日期”。
2. 第二条语句：用子查询动态生成tmpp2
sql
SELECT driver_log.name, driver_log.trav_date, driver_log.miles
FROM driver_log
INNER JOIN (-- 子查询：动态生成每个司机最新的出行日期SELECT name, max(trav_date) as trav_date FROM driver_log GROUP BY name
) AS tmpp2ON driver_log.name = tmpp2.name AND driver_log.trav_date = tmpp2.trav_date
ORDER BY driver_log.name;• tmpp2的性质：这里的tmpp2是动态生成的子查询结果，逻辑是 “对driver_log按司机分组，获取每个司机最新的出行日期（max(trav_date)）”。
• 连接逻辑：只返回driver_log中每个司机的最新出行记录（因为tmpp2存储的是每个司机的最大日期）。
• 适用场景：需要从driver_log中筛选每个司机的 “最新一条记录” 时使用（例如：查询每个司机最近一次出行的里程）。
核心区别总结
维度 第一条语句（外部表tmpp2） 第二条语句（子查询生成tmpp2）
tmpp2的来源 外部预设表（内容固定，非动态生成） 子查询动态生成（内容由driver_log数据决定）
连接的目的 筛选符合预设条件（tmpp2中的name+date）的记录 筛选每个司机的最新出行记录（max(trav_date)）
结果的决定因素 依赖tmpp2表的预设内容 依赖driver_log自身的最大日期数据
灵活性 低（tmpp2内容变更需手动修改） 高（自动适应driver_log数据变化）
12.7 计算队伍排名
mysql> select team , wins, losses from standings1-> order by wins-losses desc;
+-------------+------+--------+
| team | wins | losses |
+-------------+------+--------+
| Winnipeg | 37 | 20 |
| Crookston | 31 | 25 |
| Fargo | 30 | 26 |
| Grand Forks | 28 | 26 |
| Devils Lake | 19 | 31 |
| Cavalier | 15 | 32 |
+-------------+------+--------+
6 rows in set (0.01 sec)mysql> set @w1_diff = (select max(wins-losses) from standings1);
Query OK, 0 rows affected (0.00 sec)mysql> select team, wins as w, losses as L,-> wins/(wins+losses) as pct,-> (@w1_diff - (wins-losses)) / 2 as gb-> from standings1-> order by wins-losses desc, pct desc;
+-------------+------+------+--------+---------+
| team | w | L | pct | gb |
+-------------+------+------+--------+---------+
| Winnipeg | 37 | 20 | 0.6491 | 0.0000 |
| Crookston | 31 | 25 | 0.5536 | 5.5000 |
| Fargo | 30 | 26 | 0.5357 | 6.5000 |
| Grand Forks | 28 | 26 | 0.5185 | 7.5000 |
| Devils Lake | 19 | 31 | 0.3800 | 14.5000 |
| Cavalier | 15 | 32 | 0.3191 | 17.0000 |
+-------------+------+------+--------+---------+
6 rows in set (0.00 sec)mysql> CREATE TABLE firstplace (-> half VARCHAR(20) NOT NULL,-> division VARCHAR(50) NOT NULL,-> w1_diff INT NOT NULL,-> PRIMARY KEY (half, division)-> );
Query OK, 0 rows affected (0.03 sec)mysql> SELECT-> w1.half,-> w1.division,-> w1.team,-> w1.wins AS w,-> w1.losses AS L,-> TRUNCATE(w1.wins / (w1.wins + w1.losses), 3) AS pct,-> IF(-> fp.w1_diff = w1.wins - w1.losses,-> '-',-> TRUNCATE((fp.w1_diff - (w1.wins - w1.losses)) / 2, 1)-> ) AS gb-> FROM standings2 AS w1-> INNER JOIN firstplace AS fp-> ON w1.half = fp.half AND w1.division = fp.division-> ORDER BY-> w1.half,-> w1.division,-> w1.wins - w1.losses DESC,-> pct DESC;
Empty set (0.01 sec)mysql> SELECT-> w1.half,-> w1.division,-> w1.team,-> w1.wins AS w,-> w1.losses AS l,-> TRUNCATE(w1.wins / (w1.wins + w1.losses), 3) AS pct,-> -- 计算与榜首的差距（gb）-> IF(-> w1.wins - w1.losses = fp.max_diff, -- 若当前球队是榜首-> '-', -- 榜首的gb为'-'-> TRUNCATE((fp.max_diff - (w1.wins - w1.losses)) / 2, 1) -- 其他球队的gb-> ) AS gb-> FROM standings2 AS w1-> -- 子查询获取每个分区、半程的最大胜负差（即榜首球队的胜负差）-> INNER JOIN (-> SELECT-> half,-> division,-> MAX(wins - losses) AS max_diff -- 最大胜负差 = 榜首球队的胜负差-> FROM standings2-> GROUP BY half, division-> ) AS fp-> ON w1.half = fp.half AND w1.division = fp.division-> -- 排序：按半程、分区，再按胜负差（降序）、胜率（降序）-> ORDER BY-> w1.half,-> w1.division,-> (w1.wins - w1.losses) DESC,-> pct DESC;
+------+----------+-----------------+------+------+-------+------+
| half | division | team | w | l | pct | gb |
+------+----------+-----------------+------+------+-------+------+
| 1 | Eastern | St. Paul | 24 | 18 | 0.571 | - |
| 1 | Eastern | Thunder Bay | 18 | 24 | 0.428 | 6.0 |
| 1 | Eastern | Duluth-Superior | 17 | 24 | 0.414 | 6.5 |
| 1 | Eastern | Madison | 15 | 27 | 0.357 | 9.0 |
| 1 | Western | Winnipeg | 29 | 12 | 0.707 | - |
| 1 | Western | Sioux City | 28 | 14 | 0.666 | 1.5 |
| 1 | Western | Fargo-Moorhead | 21 | 21 | 0.500 | 8.5 |
| 1 | Western | Sioux Falls | 15 | 27 | 0.357 | 14.5 |
| 2 | Eastern | Duluth-Superior | 22 | 20 | 0.523 | - |
| 2 | Eastern | St. Paul | 21 | 21 | 0.500 | 1.0 |
| 2 | Eastern | Madison | 19 | 23 | 0.452 | 3.0 |
| 2 | Eastern | Thunder Bay | 18 | 24 | 0.428 | 4.0 |
| 2 | Western | Fargo-Moorhead | 26 | 16 | 0.619 | - |
| 2 | Western | Winnipeg | 24 | 18 | 0.571 | 2.0 |
| 2 | Western | Sioux City | 22 | 20 | 0.523 | 4.0 |
| 2 | Western | Sioux Falls | 16 | 26 | 0.380 | 10.0 |
+------+----------+-----------------+------+------+-------+------+
16 rows in set (0.01 sec)
关键修改说明
用子查询替代firstplace表：
通过 SELECT half, division, MAX(wins - losses) AS max_diff FROM standings2 GROUP BY half, division 动态计算每个分区、半程的榜首球队胜负差（无需手动创建firstplace表），避免了 “表不存在” 的错误。
gb字段计算逻辑：
若球队的胜负差（wins - losses）等于榜首的最大胜负差（max_diff），则gb为'-'（表示该队是榜首）。
否则，gb为（榜首胜负差 - 该队胜负差）/ 2（这是体育排名中计算 “场次差距” 的标准公式）。
“的原因 笔记250709)
