当前位置： 首页 > news >正文

板凳-------Mysql cookbook学习 （十一--------6)

news 2025/7/10 10:18:47

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（这是体育排名中计算 “场次差距” 的标准公式）。
http://www.lryc.cn/news/583052.html

相关文章：

  • 安卓设备信息查看器 - 源码编译
  • Android-重学kotlin(协程源码第二阶段)新学习总结
  • 中望CAD2026亮点速递（5）：【相似查找】高效自动化识别定位
  • uniapp AndroidiOS 定位权限检查
  • Android ViewModel机制与底层原理详解
  • upload-labs靶场通关详解：第19关 条件竞争（二）
  • 池化思想-Mysql异步连接池
  • 5.注册中心横向对比：Nacos vs Eureka vs Consul —— 深度解析与科学选型指南
  • Web 前端框架选型：React、Vue 和 Angular 的对比与实践
  • 华为静态路由配置
  • 小米路由器3C刷OpenWrt，更换系统/变砖恢复 指南
  • 语音识别核心模型的数学原理和公式
  • 从互联网电脑迁移Dify到内网部署Dify方法记录
  • 【编程史】IDE 是谁发明的？从 punch cards 到 VS Code
  • 计算机网络实验——访问H3C网络设备
  • Java项目集成Log4j2全攻略
  • Using Spring for Apache Pulsar：Publishing and Consuming Partitioned Topics
  • 飞算 JavaAI 智能编程助手 - 重塑编程新模态
  • bash 判断 /opt/wslibs-cuda11.8 是否为软连接, 如果是，获取连接目的目录并自动创建
  • （C++）任务管理系统（正式版）(迭代器)（list列表基础教程）（STL基础知识）
  • `fatal: bad config value for ‘color.ui‘`错误解决方案
  • ali linux 安装libreoffice
  • Markdown入门
  • 类和对象拓展——日期类
  • Django核心知识点详解：JSON、AJAX、Cookie、Session与用户认证
  • npu-smi info 华为昇腾NPU 状态监控工具解读
  • 类与对象【下篇】-- 关于类的其它语法
  • 树莓派vsftpd文件传输服务器的配置方法
  • 【02】MFC入门到精通——MFC 手动添加创建新的对话框模板
  • overleaf 改为XeLatex