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

kr98yfug 于 2023-01-01 发布在 Mysql

关注(0)|答案(1)|浏览(126)

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的快照

mysql

来源：https://stackoverflow.com/questions/74961164/what-is-wrong-in-the-below-query-query-using-cte-showing-0-output-while-using

1条答案

按热度按时间

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;

赞(0）回复(0）举报 2023-01-01

我来回答

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

1条答案

相关问题

热门标签

最新问答