mysql 下面的查询中出现了什么错误:使用CTE的查询显示0输出,而使用子查询显示预期输出

kr98yfug  于 2023-01-01  发布在  Mysql
关注(0)|答案(1)|浏览(125)
with emp as
(
    select employee_id, salary from employees where employee_id = 108 
)
select first_name, last_name, employees.salary, emp.salary from employees
left join emp
on emp.employee_id = employees.employee_id
where employees.salary > emp.salary
order by employees.salary desc;

我正尝试使用employees表为以下查询创建等效CTE

SELECT first_name,
       last_name
  FROM employees
  WHERE salary > (SELECT salary
                    FROM employees
                    WHERE employee_id = 108);

这是table的快照

gc0ot86w

gc0ot86w1#

下面的查询返回的只是记录.雇员108

with emp as
(
    select employee_id, salary from employees where employee_id = 108 
)

在下一个查询中,执行左连接employees左连接emp

select 
    first_name, last_name, employees.salary, emp.salary 
from 
    employees left join emp
      on emp.employee_id = employees.employee_id
where 
    employees.salary > emp.salary
order by 
    employees.salary desc;

因此结果没有"雇员.薪金〉雇员.薪金"的位置

Steven, King, 24'000, null
Neena, Kochlhar, 17'000, null,
...
Nancy, Greenberg, 12'000, 12'000
...
...

"12 '000〉空"不起作用
你可以试试

with emp as
(
    select employee_id, salary from employees where employee_id = 108 
)
select
    first_name, last_name, employees.salary, emp.salary 
from 
    employees inner join emp
     on (employees.salary > emp.salary)
order by 
    employees.salary desc;

相关问题