可是我不会……mysql的sql脚本:CREATE TABLE `test`.`order_info` (
`order_Id` INT( 5 ) NOT NULL ,
`order_date` DATE NOT NULL ,
`total_price` INT( 8 ) NOT NULL ,
`customer` VARCHAR( 50 ) NOT NULL
)INSERT INTO `order_info` (`order_Id`, `order_date`, `total_price`, `customer`) VALUES
(1, '2009-01-05', 524, 'zhangsan'),
(2, '2006-01-15', 3564, 'lisi'),
(3, '2006-12-13', 2314, 'wangwu'),
(4, '2008-05-13', 32514, 'lisi'),
(5, '2003-11-25', 4514, 'zhangsan'),
(6, '2000-09-03', 457, 'wangwu');
很简单的一个订单表,我想提取出每个客户最贵的订单信息--要求结果中要包含订单的日期以及订单号,不仅仅是客户姓名与最贵的价钱是多少熟悉的解答下吧
谢谢!
`order_Id` INT( 5 ) NOT NULL ,
`order_date` DATE NOT NULL ,
`total_price` INT( 8 ) NOT NULL ,
`customer` VARCHAR( 50 ) NOT NULL
)INSERT INTO `order_info` (`order_Id`, `order_date`, `total_price`, `customer`) VALUES
(1, '2009-01-05', 524, 'zhangsan'),
(2, '2006-01-15', 3564, 'lisi'),
(3, '2006-12-13', 2314, 'wangwu'),
(4, '2008-05-13', 32514, 'lisi'),
(5, '2003-11-25', 4514, 'zhangsan'),
(6, '2000-09-03', 457, 'wangwu');
很简单的一个订单表,我想提取出每个客户最贵的订单信息--要求结果中要包含订单的日期以及订单号,不仅仅是客户姓名与最贵的价钱是多少熟悉的解答下吧
谢谢!
where not exists (select 1 from `order_info` b where b.customer=a.customer and b.total_price>a.total_price);
select a.* from `order_info` a inner join (select customer,max(total_price) as max_price from `order_info` group by customer) b on a.customer=b.customer and a.total_price=b.max_price);
+----------+------------+-------------+----------+
| order_Id | order_date | total_price | customer |
+----------+------------+-------------+----------+
| 1 | 2009-01-05 | 524 | zhangsan |
| 2 | 2006-01-15 | 3564 | lisi |
| 3 | 2006-12-13 | 2314 | wangwu |
| 4 | 2008-05-13 | 32514 | lisi |
| 5 | 2003-11-25 | 4514 | zhangsan |
| 6 | 2000-09-03 | 457 | wangwu |
+----------+------------+-------------+----------+
6 rows in set (0.00 sec)mysql> select *
-> from order_info a
-> where not exists (select 1 from order_info where customer=a.customer and
total_price>a.total_price);
+----------+------------+-------------+----------+
| order_Id | order_date | total_price | customer |
+----------+------------+-------------+----------+
| 3 | 2006-12-13 | 2314 | wangwu |
| 4 | 2008-05-13 | 32514 | lisi |
| 5 | 2003-11-25 | 4514 | zhangsan |
+----------+------------+-------------+----------+
3 rows in set (0.06 sec)mysql>
select a.*
from `order_info` a
inner join (select customer,max(total_price) as max_price from `order_info` group by customer) b on a.customer=b.customer and a.total_price=b.max_price;