表:
CREATE TABLE `fruits` (
`type` varchar(10) DEFAULT NULL,
`variety` varchar(10) DEFAULT NULL,
`price` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
数据:
INSERT INTO `fruits` (`type`, `variety`, `price`)
VALUES
('apple', 'gala', 2.79),
('apple', 'fuji', 0.24),
('apple', 'limbertwig', 2.87),
('orange', 'valencia', 3.59),
('orange', 'navel', 9.36),
('pear', 'bradford', 6.05),
('pear', 'bartlett', 2.14),
('cherry', 'bing', 2.55),
('cherry', 'chelan', 6.33);
查询
select type, variety, price
from fruits
where price = (select min(price) from fruits as f where f.type = fruits.type)
or price = (select min(price) from fruits as f where f.type = fruits.type
and price > (select min(price) from fruits as f2 where f2.type = fruits.type));
结果:
apple gala 2.79
apple fuji 0.24
orange valencia 3.59
orange navel 9.36
pear bradford 6.05
pear bartlett 2.14
cherry bing 2.55
cherry chelan 6.33
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。