前两天同学遇到这样一个需求。用sql找出成绩表中 各科前10名学生名单。 成绩表字典我就不说明了,大家都懂。
他说只用sql处理。我当时首先想到的是存储过程、游标之类的。
过后,他又提起这个事情来。告诉我说不用存储过程就可以。然后给我贴了实例出来。
DROP TABLE IF EXISTS `ads_credit_applylog`;
CREATE TABLE `ads_credit_applylog` (
`id` varchar(20) NOT NULL,
`customer_id` varchar(20) DEFAULT NULL,
`ips` int(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of ads_credit_applylog
-- ----------------------------
INSERT INTO `ads_credit_applylog` VALUES ('1', '15588', '921');
INSERT INTO `ads_credit_applylog` VALUES ('2', '15588', '597');
INSERT INTO `ads_credit_applylog` VALUES ('3', '15588', '483');
INSERT INTO `ads_credit_applylog` VALUES ('4', '15588', '1000');
INSERT INTO `ads_credit_applylog` VALUES ('5', '12619', '421');
INSERT INTO `ads_credit_applylog` VALUES ('6', '12619', '358');
INSERT INTO `ads_credit_applylog` VALUES ('7', '12619', '523');
INSERT INTO `ads_credit_applylog` VALUES ('8', '12619', '879');
select (@i := case when @pre_parent_code=customer_id then @i + 1 else 1 end ) rownum,
customer_id,ips,(@pre_parent_code:=customer_id)
from
#ads_credit_applylog ,
#需要先排好序
(select customer_id,ips from ads_credit_applylog order by customer_id,ips desc ) a ,
(SELECT @i := 0, @pre_parent_code:='') AS b
group by customer_id ,ips desc
order by customer_id,ips desc,rownum
利用临时表来解决排序问题。
运行结果如下。