SQL实战练习题(6)-制作工资升序排名-leetcode sql 178

目录

• 数据SQL
• 答案

数据SQL

CREATE TABLE `Employee` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  salary int DEFAULT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO Employee (name,salary) VALUES
	 ('joe',70000),
	 ('henry',80000),
	 ('sam',60000),
	 ('max',90000),
	 ('john',90000),
	 ('jobs',80000),
	 ('white',60000),
	 ('smith',90000),
	 ('adam',90000),
	 ('jack',8500),
	 ('bon',60000),
	 ('pony',90000);

答案

/*
 * 制作工资倒序排名
 * */
SELECT
	id, name,
	e2.salary
	,(select count(DISTINCT e1.salary)	from Employee e1 where e1.salary >= e2.salary) as rk
FROM
	Employee e2
order by rk
;