mysql sql 分组排序_mysql分组排序

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的 有时间再加。

分享到：

2014-02-10 14:22

浏览 1378

分类:数据库

评论