mysql - 确定为 SQL MAX() 函数返回的源行
问题描述
假设我有下表:
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| book_id | book_name | isbn_no | cate_id | aut_id | pub_id | dt_of_pub | pub_lang | no_page | book_price |
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| BK001 | Introduction to Electrodynamics | 0000979001 | CA001 | AUT001 | P003 | 2001-05-08 | English | 201 | 85.00 |
| BK002 | Understanding of Steel Construction | 0000979002 | CA002 | AUT002 | P001 | 2003-07-15 | English | 300 | 105.50 |
| BK003 | Guide to Networking | 0000979003 | CA003 | AUT003 | P002 | 2002-09-10 | Hindi | 510 | 200.00 |
| BK004 | Transfer of Heat and Mass | 0000979004 | CA002 | AUT004 | P004 | 2004-02-16 | English | 600 | 250.00 |
| BK005 | Conceptual Physics | 0000979005 | CA001 | AUT005 | P006 | 2003-07-16 | NULL | 345 | 145.00 |
| BK006 | Fundamentals of Heat | 0000979006 | CA001 | AUT006 | P005 | 2003-08-10 | German | 247 | 112.00 |
| BK007 | Advanced 3d Graphics | 0000979007 | CA003 | AUT007 | P002 | 2004-02-16 | Hindi | 165 | 56.00 |
| BK008 | Human Anatomy | 0000979008 | CA005 | AUT008 | P006 | 2001-05-17 | German | 88 | 50.50 |
| BK009 | Mental Health Nursing | 0000979009 | CA005 | AUT009 | P007 | 2004-02-10 | English | 350 | 145.00 |
| BK010 | Fundamentals of Thermodynamics | 0000979010 | CA002 | AUT010 | P007 | 2002-10-14 | English | 400 | 225.00 |
| BK011 | The Experimental Analysis of Cat | 0000979011 | CA004 | AUT011 | P005 | 2007-06-09 | French | 225 | 95.00 |
| BK012 | The Nature of World | 0000979012 | CA004 | AUT005 | P008 | 2005-12-20 | English | 350 | 88.00 |
| BK013 | Environment a Sustainable Future | 0000979013 | CA004 | AUT012 | P001 | 2003-10-27 | German | 165 | 100.00 |
| BK014 | Concepts in Health | 0000979014 | CA005 | AUT013 | P004 | 2001-08-25 | NULL | 320 | 180.00 |
| BK015 | Anatomy & Physiology | 0000979015 | CA005 | AUT014 | P008 | 2000-10-10 | Hindi | 225 | 135.00 |
| BK016 | Networks and Telecommunications | 00009790_16 | CA003 | AUT015 | P003 | 2002-01-01 | French | 95 | 45.00 |
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
我的查询是:
SELECT cate_id, MAX( book_price)
FROM book_mast
GROUP BY cate_id;
它返回:
+---------+-----------------+
| cate_id | MAX(book_price) |
+---------+-----------------+
| CA001 | 145.00 |
| CA002 | 250.00 |
| CA003 | 200.00 |
| CA004 | 100.00 |
| CA005 | 180.00 |
+---------+-----------------+
现在说第一条记录 - CA001,最高书价 145。
如何确定它是 book_id=BK005 的概念物理学?
如果有两条记录匹配相同的最大值会发生什么?
(这个问题是我试图解决的一个大问题的一部分,很抱歉从https://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-中无耻地复制示例grouping-max-with-group-by.php。这只是为了解释问题)
解决方案
SELECT bm1.book_id
, bm1.book_name
, bm1.cate_id
, bm1.book_price
FROM book_mast bm1
where bm1.book_price = (SELECT MAX( bm2.book_price )
FROM book_mast bm2
where bm2.cate_id = bm1.cate_id);
这是他的示例的一个小演示:DEMO
推荐阅读
- react-native - React Native Expo FacebookAds 在 IOS 顶部有额外空间
- ruby-on-rails - 注册信息保存到数据库前付款
- ruby-on-rails - ActiveRecord::ConnectionNotEstablished: ActiveRecord::Base 与 mongoid 没有连接池
- ios - 从数据库中检索数据后重新加载 UiTableView - Swift
- reactjs - 从一个类中获取数据以使其成为 stackNavigator 选项卡的标题?
- python-2.7 - CodeHS 真的很奇怪的问题
- sql-server - SSIS SQL任务不返回字符串日期
- sql - SAS proq SQL:有条件地求和和折叠行
- python - 使用 cython 从多个 pyx 文件制作可执行文件
- python - Pandas groupby 然后填充缺失的行