Mysql根据指定字段的int值查出在当前列表的排名
先看表结构和数据:
DROP TABLE IF EXISTS `ndb_record`; CREATE TABLE `ndb_record` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '测量记录', `user_id` bigint(20) NOT NULL COMMENT '用户id', `yellow` int(11) DEFAULT NULL COMMENT '黄色状态持续时长', `green` int(11) DEFAULT NULL COMMENT '绿色状态持续时长', `blue` int(11) DEFAULT NULL COMMENT '蓝色状态时长', `create_time` date DEFAULT NULL COMMENT '测量时间', `week` varchar(20) DEFAULT NULL COMMENT '周几', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of ndb_record -- ---------------------------- INSERT INTO `ndb_record` VALUES ('17', '13', '8', '7', '6', '2017-03-23', '星期四'); INSERT INTO `ndb_record` VALUES ('18', '13', '8', '7', '6', '2017-03-22', '星期三'); INSERT INTO `ndb_record` VALUES ('19', '13', '8', '7', '6', '2017-03-20', '星期一'); INSERT INTO `ndb_record` VALUES ('20', '13', '8', '7', '6', '2017-03-19', '星期日'); INSERT INTO `ndb_record` VALUES ('21', '13', '8', '7', '6', '2017-03-18', '星期六'); INSERT INTO `ndb_record` VALUES ('22', '13', '8', '7', '8', '2017-03-23', '星期四'); INSERT INTO `ndb_record` VALUES ('23', '13', '8', '7', '1', '2017-03-20', '星期一'); INSERT INTO `ndb_record` VALUES ('24', '13', '8', '7', '2', '2017-03-14', '星期二'); INSERT INTO `ndb_record` VALUES ('25', '13', '8', '7', '3', '2017-03-17', '星期五'); INSERT INTO `ndb_record` VALUES ('26', '13', '8', '7', '4', '2017-03-16', '星期四'); INSERT INTO `ndb_record` VALUES ('27', '12', '8', '7', '4', '2017-03-21', '星期二'); INSERT INTO `ndb_record` VALUES ('28', '12', '8', '7', '4', '2017-03-20', '星期一'); INSERT INTO `ndb_record` VALUES ('29', '12', '8', '7', '4', '2017-03-20', '星期一'); INSERT INTO `ndb_record` VALUES ('30', '12', '6', '7', '4', '2017-03-19', '星期日'); INSERT INTO `ndb_record` VALUES ('31', '12', '6', '7', '3', '2017-03-18', '星期六'); INSERT INTO `ndb_record` VALUES ('32', '16', '6', '7', '3', '2017-03-16', '周四'); INSERT INTO `ndb_record` VALUES ('33', '16', '6', '7', '3', '2017-03-31', '周五'); INSERT INTO `ndb_record` VALUES ('34', '16', '6', '6', '0', '2017-04-05', '周三');
她给出的问题是,通过这条Sql语句统计了每个字段的总和,然后找出指定user_id关联times总和的排名
SELECT user_id,(SUM(yellow)+SUM(green)+SUM(blue)) AS times FROM ndb_record GROUP BY user_id;
查询出的结果是:
我给出了两种方法一条SQL实现。
第一种
SELECT o_d FROM (SELECT a.*, @rownum := @rownum + 1 AS o_d FROM ( SELECT user_id,(SUM(yellow)+SUM(green)+SUM(blue)) AS times FROM ndb_record GROUP BY user_id ORDER BY times DESC ) a, (SELECT @rownum := 0) r) b WHERE user_id =13
第二种
SELECT count(*) AS o_d FROM ( SELECT user_id, ( SUM(yellow) + SUM(green) + SUM(blue) ) AS times FROM ndb_record GROUP BY user_id ) a WHERE times >= ( SELECT times FROM ( SELECT user_id, ( SUM(yellow) + SUM(green) + SUM(blue) ) AS times FROM ndb_record GROUP BY user_id ) b WHERE `user_id` = 13 )
查询结果也是跟第一种一样。
可能以上说明您没太明白,然后我再拿一条简单的表举例:
CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `name` varchar(22) NOT NULL DEFAULT '' COMMENT '姓名', `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
表创建好了,看后查看一下结果SELECT * FROM test:
比如,我们要查的是王五在这五个人里年龄排第几,目测赵六是老大,也就是排名第一,刘七老五,排名第五。
上语句:
select * from (SELECT t.*, @rownum := @rownum + 1 AS o_d FROM ( select * from test order by age desc ) t, (SELECT @rownum := 0) r) b where id =1
查询的条件是id=1,也就是张三,结果是4.
SELECT count(*) AS o_d FROM (SELECT age FROM test) a WHERE age >= (SELECT age FROM (SELECT * FROM test) b WHERE `id`='1');
完毕。
虽然结果出来了,还请前辈们多多指教哪里的不足!致敬!~