以“as”宣布的列不存在 SQL

发布于 2025-01-15 14:32:24 字数 993 浏览 6 评论 0原文

我使用“作为预算”列，但是之后当尝试在报告“没有列预算”的“时间”部分中使用它时。

这是代码：

select p.id,
p.budget/365 as budget,
SUM(e.salary/365) as required_sum
from linkedin_projects p
JOIN linkedin_emp_projects as ep ON ep.project_id = p.id
JOIN linkedin_employees as e ON e.id = ep.emp_id
WHERE budget < required_sum
GROUP BY ep.project_id, p.id, p.budget

控制台输出：

(psycopg2.errors.UndefinedColumn) column "required_sum" does not exist
LINE 8: WHERE budget < required_sum
                       ^

[SQL: --datediff(p.end_date, p.start_date)
select p.id,
p.budget/365 as budget,
SUM(e.salary/365) as required_sum
from linkedin_projects p
JOIN linkedin_emp_projects as ep ON ep.project_id = p.id
JOIN linkedin_employees as e ON e.id = ep.emp_id
WHERE budget < required_sum
GROUP BY ep.project_id, p.id, p.budget]
(Background on this error at: http://sqlalche.me/e/f405)

可能会返回错误，因为您可能对字符串文本使用了双引号。请对字符串文本使用单引号。双引号用于表或字段的名称。

原文

I used column "as budget", however after that when trying to use it in when section in reports that "There is no column budget".

Here is the code:

select p.id,
p.budget/365 as budget,
SUM(e.salary/365) as required_sum
from linkedin_projects p
JOIN linkedin_emp_projects as ep ON ep.project_id = p.id
JOIN linkedin_employees as e ON e.id = ep.emp_id
WHERE budget < required_sum
GROUP BY ep.project_id, p.id, p.budget

Output from console:

(psycopg2.errors.UndefinedColumn) column "required_sum" does not exist
LINE 8: WHERE budget < required_sum
                       ^

[SQL: --datediff(p.end_date, p.start_date)
select p.id,
p.budget/365 as budget,
SUM(e.salary/365) as required_sum
from linkedin_projects p
JOIN linkedin_emp_projects as ep ON ep.project_id = p.id
JOIN linkedin_employees as e ON e.id = ep.emp_id
WHERE budget < required_sum
GROUP BY ep.project_id, p.id, p.budget]
(Background on this error at: http://sqlalche.me/e/f405)

An error may have been returned because you probably used double quotes for string text. Please use single quotes for string text. Double quotes are for names of tables or fields.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

心头的小情儿 2025-01-22 14:32:24

用这个

select * from (
select p.id,
p.budget/365 as budget,
SUM(e.salary/365) as required_sum
from linkedin_projects p
JOIN linkedin_emp_projects as ep ON ep.project_id = p.id
JOIN linkedin_employees as e ON e.id = ep.emp_id
GROUP BY ep.project_id, p.id, p.budget
)a
WHERE budget < required_sum

use this

select * from (
select p.id,
p.budget/365 as budget,
SUM(e.salary/365) as required_sum
from linkedin_projects p
JOIN linkedin_emp_projects as ep ON ep.project_id = p.id
JOIN linkedin_employees as e ON e.id = ep.emp_id
GROUP BY ep.project_id, p.id, p.budget
)a
WHERE budget < required_sum

回复收藏 0 原文

明媚殇 2025-01-22 14:32:24

where 子句无法访问由 group by 子句计算的信息，您需要使用 having 子句来执行此操作：

SELECT
      p.id
    , p.budget/365 AS budget
    , SUM(e.salary/365) AS required_sum
FROM linkedin_projects p
JOIN linkedin_emp_projects AS ep ON ep.project_id = p.id
JOIN linkedin_employees AS e ON e.id = ep.emp_id
GROUP BY
      p.id
    , p.budget/365
HAVING p.budget/365 < required_sum

The where clause cannot access information that is calculated by the group by clause, you need to use a having clause to do that:

SELECT
      p.id
    , p.budget/365 AS budget
    , SUM(e.salary/365) AS required_sum
FROM linkedin_projects p
JOIN linkedin_emp_projects AS ep ON ep.project_id = p.id
JOIN linkedin_employees AS e ON e.id = ep.emp_id
GROUP BY
      p.id
    , p.budget/365
HAVING p.budget/365 < required_sum

回复收藏 0 原文

~没有更多了~