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)
