题解 | #对于employees表中，给出奇数行的first_name#
对于employees表中，给出奇数行的first_name
http://www.nowcoder.com/practice/e3cf1171f6cc426bac85fd4ffa786594
【初始代码】
select t.first_name from
(select first_name, rank() over(order by first_name) as r from employees) as t
where mod(t.r,2)=1
报错，因为题目要求不要排序输出，而上述代码最终输出结果是排序之后的
【修改代码】
select t.first_name from
employees as e left join
(select employees.emp_no, employees.first_name, rank() over(order by employees.first_name) as r from employees) as t
on e.emp_no = t.emp_no
where mod(t.r,2)=1
为什么用emp_no链接不行。最终得到的结果也是经过排序的,不懂为什么会这样子。
【最终代码】
select t.first_name from
employees as e left join
(select employees.first_name, rank() over(order by employees.first_name) as r from employees) as t
on e.first_name = t.first_name
where mod(t.r,2)=1
这样就没问题
【使用exist】
SELECT e.first_name
FROM employees e
WHERE EXISTS (
SELECT t.first_name
FROM (
SELECT first_name, emp_no,
ROW_NUMBER()OVER(ORDER BY first_name) AS flag
FROM employees) AS t
WHERE t.emp_no = e.emp_no
AND t.flag%2 = 1)
【使用in】
SELECT e.first_name
FROM employees e
WHERE e.first_name IN (
SELECT t.first_name
FROM (
SELECT first_name, emp_no,
ROW_NUMBER()OVER(ORDER BY first_name) AS flag
FROM employees) AS t
WHERE t.flag%2 = 1)
