首页 > 代码库 > Mysql根据指定字段的int值查出在当前列表的排名

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

2024-09-22 23:09:08   215人阅读

先看表结构和数据：

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

完毕。

 

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

 

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


联系
我们