Mysql如何查出排名 Nth的数据

原创

wx636b275beaa50 2022-11-09 13:42:01 博主文章分类：Mysql ©著作权

©著作权归作者所有：来自51CTO博客作者wx636b275beaa50的原创作品，请联系作者获取转载授权，否则将追究法律责任

初始化数据

CREATE TABLE `employee` (
  `ID` int(11) DEFAULT NULL,
  `NAME` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `DEPARTMENT` int(11) DEFAULT NULL,
  `EMAIL` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


INSERT INTO EMPLOYEE VALUES (1,'Tl1',101,'Tl1@ls.com',2536.46);
INSERT INTO EMPLOYEE VALUES (2,'Tl2',102,'Tl2h@ls.com',3536.46);
INSERT INTO EMPLOYEE VALUES (3,'Tl3',103,'Tl3h@ls.com',5536.46);
INSERT INTO EMPLOYEE VALUES (4,'Tl4',104,'Tl4h@ls.com',5536.46);
INSERT INTO EMPLOYEE VALUES (5,'Tl1',105,'Tl1@ls.com',2536.46);
INSERT INTO EMPLOYEE VALUES (6,'Tl2',106,'Tl2h@ls.com',3656.46);
INSERT INTO EMPLOYEE VALUES (7,'Tl3',107,'Tl3h@ls.com',4626.46);
INSERT INTO EMPLOYEE VALUES (8,'Tl4',108,'Tl4h@ls.com',3214.46);

Mysql如何查出排名 Nth的数据_sql

查询SQl

SELECT *
FROM employee Emp1
WHERE (2) = (
   SELECT COUNT( DISTINCT ( Emp2.salary ) )
      FROM employee Emp2
     WHERE Emp2.salary >= Emp1.salary
      )

Mysql如何查出排名 Nth的数据_sql_02