记录一下分组排序:
数据库结构如下:
DROP TABLE IF EXISTS `stock`;
CREATE TABLE `stock` (
`ID` int(11) NOT NULL auto_increment,
`Name` varchar(255) default NULL,
`value` int(11) default NULL,
`Date` date default NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of stock
-- ----------------------------
INSERT INTO `stock` VALUES ('1', '股票1', '10', '2013-12-25');
INSERT INTO `stock` VALUES ('2', '股票1', '9', '2013-12-26');
INSERT INTO `stock` VALUES ('3', '股票1', '8', '2013-12-25');
INSERT INTO `stock` VALUES ('4', '股票1', '7', '2013-12-24');
INSERT INTO `stock` VALUES ('5', '股票1', '5', '2013-12-23');
INSERT INTO `stock` VALUES ('6', '股票1', '4', '2013-12-22');
INSERT INTO `stock` VALUES ('7', '股票2', '19', '2013-12-26');
INSERT INTO `stock` VALUES ('8', '股票2', '18', '2013-12-25');
INSERT INTO `stock` VALUES ('9', '股票2', '17', '2013-12-24');
INSERT INTO `stock` VALUES ('10', '股票2', '15', '2013-12-23');
INSERT INTO `stock` VALUES ('11', '股票2', '14', '2013-12-22');
INSERT INTO `stock` VALUES ('12', '股票3', '109', '2013-12-26');
INSERT INTO `stock` VALUES ('13', '股票3', '108', '2013-12-25');
INSERT INTO `stock` VALUES ('14', '股票3', '107', '2013-12-24');
INSERT INTO `stock` VALUES ('15', '股票3', '105', '2013-12-23');
INSERT INTO `stock` VALUES ('16', '股票3', '104', '2013-12-22');
目标是找出每个股票排名前n的记录.排名前三的使用SQL如下:
select *,(select count(*) from stock s where s.name=s1.name and s.value>s1.value) as test from stock s1 having test<=2
窍门是表自己跟自己联立,并且这个sql中的子查询是没办法单独运行的,要把整体的SQL结构记下来。
应该还有用top 3 或者mysql的limit的 有时间再加。