SQL255 给出employees表中排名为奇数行的first_name
- 题目
- 题解(94)
- 讨论(391)
- 排行
- 面经
new
较难 通过率:26.78% 时间限制:1秒 空间限制:32M
描述
对于employees表中,输出first_name排名(按first_name升序排序)为奇数的first_name
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
如,输入为:
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
输出格式:
first |
Georgi |
Anneke |
请你在不打乱原序列顺序的情况下,输出:按first_name排升序后,取奇数行的first_name。
如对以上示例数据的first_name排序后的序列为:Anneke、Bezalel、Georgi、Kyoichi。
则原序列中的Georgi排名为3,Anneke排名为1,所以按原序列顺序输出Georgi、Anneke。
示例1
输入:
drop table if exists `employees` ; CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` char(1) NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`)); INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26'); INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21'); INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12'); INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
复制输出:
Georgi Anneke
开窗:
select
first_name `first`
from
(
select
first_name,row_number() over() rn1,row_number() over(order by first_name ) rnb
from
employees
order by rn1
) s1
where rnb % 2 = 1
不开窗
SELECT e1.first_name
FROM employees e1
where (SELECT COUNT(1)
FROM employees e2
WHERE e1.first_name >= e2.first_name) % 2 = 1