sql查询某个类型前几_求职面试_非技术区_程序员俱乐部

面试比较常问的sql问题，sql server2005，oracle有比较好的处理方式有待以后总结下
下面是mysql下的测试查询memo分类，日期最新的前两条数据，不论dateline字段是varchar
还是int，datetime类型都可以直接比较大小排序
表如下
CREATE TABLE `table1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(4000) NOT NULL,
  `memo` varchar(5000) NOT NULL,
  `dateline` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=UTF8;

insert  into `table1`(`id`,`name`,`memo`,`dateline`) values (1,'a1','a','2010-11-01'),(2,'a2','a','2010-11-02'),(3,'a3','a','2010-11-03'),(4,'c1','c','2010-11-01'),(5,'c2','c','2010-11-02'),(6,'c3','c','2010-11-03'),(7,'b1','b','2010-11-01'),(8,'b2','b','2010-11-02'),(9,'b3','b','2010-11-03');

sql语句
SELECT * FROM table1 t1
WHERE
2>(SELECT COUNT(id) FROM table1 WHERE memo=t1.memo AND dateline<t1.dateline)
ORDER BY t1.memo, t1.dateline