假设我有下表:
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| 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\u id=bk005确定它是概念物理?
如果有两个记录匹配相同的最大值,会发生什么情况?
(这个问题是我试图解决的大视图的一部分,很抱歉无耻地从https://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-max-with-group-by.php. 这只是为了解释问题)
3条答案
按热度按时间xmq68pz91#
您可以使用groupby来获取每个cate\u id的最高价格,然后使用inner join与原始表连接来获取图书id
qcbq4gxm2#
您需要在聚集的结果和原始值之间建立连接
c90pui9n3#
下面是他的一个小示例:demo