Mysql根据指定字段的int值查出在当前列表的排名

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‘);

完毕。

 

虽然结果出来了，还请前辈们多多指教哪里的不足！致敬！~

 

Mysql根据指定字段的int值查出在当前列表的排名

标签：时间   ble   images   logs   sql   nbsp   alt   exist   utf8