Mysql根據指定欄位的int值查出在當前列表的排名

OldBoy~發表於2017-05-17
MySql

先看錶結構和資料：

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

完畢。

 

雖然結果出來了，還請前輩們多多指教哪裡的不足！致敬！~

 

相關文章