Sql实战-分组查询排序后取出前n条记录

CREATE TABLE `products` (
`id` int(11) NOT NULL,
`name` varchar(100) DEFAULT NULL,
`category` varchar(100) DEFAULT NULL,
`type` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `products` VALUES
('1', 'Apple', 'Fruit', '1'), 
('2', 'Banana', 'Fruit', '1'), 
('3', 'Carrot', 'Vegetable', '2'), 
('4', 'Tomato', 'Vegetable', '2'), 
('5', 'Chicken', 'Meat', '3'), 
('6', 'Beef', 'Meat', '4')

select  category,type,GROUP_CONCAT(id) as ids from
products GROUP BY category,type;

select  a.*  from  products a inner join (
select  category,type from products where category ='Fruit'
and type='1' GROUP BY category,type
) b on a.category=b.category and a.type=b.type

select  *  from  products a  where
exists (
   select 1 from products  where
   a.category=category and a.id>id )

select  *  from  products a where a.id in 
(select MAX(id) from products GROUP BY category)

select *  from  students a where exists 
(select count(1) from students where name=a.name and score>a.score 
 having count(1)<2) order by a.name