我正在将MySQL数据库的结果转换为Postgresql数据库，并发现随机语法问题，搜索没有帮助。
对于这个:我试图返回一个结果列表的用户谁没有在我们的系统中创建一个工作超过90天。我已经尝试了一些变化，但这里是我的代码现在:
select law_firms.name as "Law Firm"
from law_firms
left join jobs on jobs.law_firm_id = law_firms.id
where jobs.id IN (SELECT jobs.created_at::date >= 90) IS NULL
and law_firms.deleted_at IS NULL
and law_firms.name not like '%Temp%"'
and law_firms.name not like '%Individual%'
GROUP BY law_firms.name
我尝试了::date between和cast的几个不同的变体，但是它返回的错误消息是" error: operator does not exist: date>= integer提示:没有操作符匹配给定的名称和参数类型。您可能需要添加显式类型转换">
作为参考，最初在我们的BI中工作的MySQL代码是:
where `jobs`.`id` IN (SELECT `jobs`.`created_at` BETWEEN NOW() - INTERVAL 90 DAY AND NOW()) IS NULL
解决策略
- 获取每家律师事务所的最新职位信息
- 过滤掉最近的作业，小于90天
- 连接表law_firm
每个律师事务所最近的工作
SELECT DISTINCT ON (law_firm_id) law_firm_id, created_at
FROM job
ORDER BY law_firm_id, created_at DESC
SELECT DISTINCT ON (law_firm_id)- 将返回每个law_firm_id一次
- 它将按照
ORDER BY的顺序为law_firm_id选择第一行 ORDER BY [...] created_at DESC将返回law_firm_id 最年轻的作业
Postgres Docu: https://www.postgresql.org/docs/current/sql-select.html#SQL-DISTINCT
过滤入职少于90天的律师事务所
使用INTERVAL:NOW() - INTERVAL '90 days'计算postgres中的截止日期
SELECT j.*
FROM (
SELECT DISTINCT ON (law_firm_id) law_firm_id, created_at
FROM job
ORDER BY law_firm_id, created_at DESC
) as j
WHERE j.created_at < NOW() - INTERVAL '90 days'
NOW() - INTERVAL '90 days'-计算截止时间created_at假设它是timestamp或timestamp with time zone
Postgres Docu: https://www.postgresql.org/docs/current/functions-datetime.html
最终查询SELECT law_firms.name as "Law Firm"
FROM law_firms
JOIN (
SELECT DISTINCT ON (law_firm_id) law_firm_id, created_at
FROM job
ORDER BY law_firm_id, created_at DESC
) as j
ON j.law_firm_id = law_firms.id
WHERE j.created_at < NOW() - INTERVAL '90 days'
AND law_firms.deleted_at IS NULL
AND law_firms.name not like '%Temp%"'
AND law_firms.name not like '%Individual%'
- 查询不使用
IN，不幸的是IN在postgres中不是很快(例如:postgres查询with in是非常慢的) - 我以为你想要所有至少有过一次工作的公司。如果您需要的是所有没有工作的律师事务所，那么查询将是错误的。
请尝试以下查询:
select "Law Firm" from (
select law_firms.id, law_firms.name "Law Firm", count(jobs.id) "rec_count"
from law_firms
left join jobs on jobs.law_firm_id = law_firms.id
where jobs.created_at::date between (current_date- interval '90 days') and current_date
and law_firms.deleted_at IS NULL
and law_firms.name not like '%Temp%"'
and law_firms.name not like '%Individual%'
GROUP BY 1,2
) tab
where rec_count=0
在上述查询中，子查询将获得过去90天内律师事务所所有JOB id的计数。然后，我们可以过滤记录计数为零的记录，以获得所需的结果。
除此之外:总是使用小写字母为你的列，表等命名。否则你必须用"always包围它。
根据我的评论，这不是像转换带括号的子查询
那么简单吗?来自:
SELECT `jobs`.`created_at` BETWEEN NOW() - INTERVAL 90 DAY AND NOW()
:
SELECT jobs.created_at BETWEEN NOW() - interval '90 days' AND NOW()
编辑:当然，您需要添加WHERE和FROM来匹配原始子查询，但您的示例原始子查询不包括它。
我想它应该是这样的:
SELECT jobs.created_at
FROM jobs
WHERE jobs.created_at BETWEEN NOW() - INTERVAL '90 DAY' AND NOW()