SELECT * FROM shop s1 WHERE price = (SELECT MAX(price) FROM shop s2 WHERE s1.article=s2.article);
来源:互联网 发布:win10网络连接不可用 编辑:程序博客网 时间:2024/04/28 12:42
3.6.4. 拥有某个字段的组间最大值的行
任务:对每项物品,找出最贵价格的物品的经销商。
可以用这样一个子查询解决该问题:
SELECT article, dealer, priceFROM shop s1WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article);
+---------+--------+------------+
| article | dealer | MAX(price) |
+---------+--------+------------+
| 0001 | A | 3.99 |
| 0002 | A | 10.99 |
| 0003 | B | 1.69 |
| 0004 | D | 19.95 |
+---------+--------+------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM shop s1 WHERE price = (SELECT MAX(price) FROM shop s2 WHERE s1.article=s2.article);
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | C | 1.69 |
| 0004 | D | 19.95 |
+---------+--------+-------+
4 rows in set (0.00 sec)
mysql> explain SELECT * FROM shop s1 WHERE price = (SELECT MAX(price) FROM shop s2 WHERE s1.article=s2.article);
+----+--------------------+-------+------+---------------+---------+---------+---------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+---------------+---------+---------+---------------------+------+-------------+
| 1 | PRIMARY | s1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
| 2 | DEPENDENT SUBQUERY | s2 | ref | PRIMARY | PRIMARY | 4 | duba_nav.s1.article | 1 | NULL |
+----+--------------------+-------+------+---------------+---------+---------+---------------------+------+-------------+
2 rows in set (0.00 sec)
mysql> explain SELECT article ,dealer, MAX(price) FROM shop GROUP BY article;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
| 1 | SIMPLE | shop | index | PRIMARY | PRIMARY | 24 | NULL | 7 | NULL |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)
- SELECT * FROM shop s1 WHERE price = (SELECT MAX(price) FROM shop s2 WHERE s1.article=s2.article);
- s1 == s2与 s1.equals(s2)
- strSQL = "Select * From " & strTableName & " Where False "
- JdbcTemplate (select * from xx where id = ?)
- select * from table where 1=1
- select * from table where 1=1
- select * from table where 1=1
- select * from table where 1=1
- select from table where 1=1
- select * from table where 1=1
- select * from sample where id = id
- select * from book where 1=1
- c# from where select
- short s1 = 1; s1 = s1 + 1; short s2 = 1; s2 += 1;
- s1 = (short) (s1 + 1) 与s2 += 1
- String s1==s2面试题
- 比较字符串s1和s2,若s1>s2,输出一个正数,若s1=s2,输出0,若s1<s2,输出一个负数。不用strcpy函数
- 获取最新(最大)值的一条数据select * from dbname where id=(select max(id) from dbname)
- 函数中的关键字及作用域
- EXTJS4扩展实例:如何使用filter查询treepanel
- 开发如何选择。。。网银在线,快钱,支付宝,财付通
- python基础教程_学习笔记17:标准库:一些最爱——time
- Linux下logrotate 配置
- SELECT * FROM shop s1 WHERE price = (SELECT MAX(price) FROM shop s2 WHERE s1.article=s2.article);
- 大小端存储判断
- python socket 实现服务器/客户端通信
- Ext与spring mvc集成时间格式问题处理
- MFC 工具栏的自定义创建
- [SQL Server] sp_who, sp_who2和sp_who3
- 一、从Java、C#到C++ (为什么C++比较难)
- JSP 教程
- IE浏览器UserAgent字符串错误