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

標籤：時間   ble   images   logs   sql   nbsp   alt   exist   utf8   

先看錶結構和資料：

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_dFROM    (        SELECT            user_id,            (                SUM(yellow) + SUM(green) + SUM(blue)            ) AS times        FROM            ndb_record        GROUP BY            user_id    ) aWHERE    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值查出在當前列表的排名