如何将 MS Access 查询转换为 PostgreSql 查询？

Question

我将 MS Access 数据库中的数据转移到 PostgreSQL 数据库中。我想在 PostgreSQL 中复制我在 MS Access 中所做的查询。在我的一个表的 SQL 视图中，我有：

SELECT GENERAL_CAUSE_NFD.[Cause Class], DFS_FIRE_ARCHIVE.FIRE_YEAR AS [Year], OBJECTIVES_NFD.[Response Category], Count(DFS_FIRE_ARCHIVE.REGION) AS Total
FROM (((DFS_FIRE_ARCHIVE INNER JOIN GENERAL_CAUSE_ORDER ON DFS_FIRE_ARCHIVE.GENERAL_CAUSE = GENERAL_CAUSE_ORDER.GENERAL_CAUSE) INNER JOIN OBJECTIVE_ORDER ON DFS_FIRE_ARCHIVE.OBJECTIVE = OBJECTIVE_ORDER.OBJECTIVE) INNER JOIN OBJECTIVES_NFD ON OBJECTIVE_ORDER.OBJECTIVE = OBJECTIVES_NFD.OBJECTIVE) INNER JOIN GENERAL_CAUSE_NFD ON GENERAL_CAUSE_ORDER.GENERAL_CAUSE = GENERAL_CAUSE_NFD.GENERAL_CAUSE
GROUP BY GENERAL_CAUSE_NFD.[Cause Class], DFS_FIRE_ARCHIVE.FIRE_YEAR, OBJECTIVES_NFD.[Response Category], DFS_FIRE_ARCHIVE.GENERAL_CAUSE, DFS_FIRE_ARCHIVE.OBJECTIVE, GENERAL_CAUSE_ORDER.ORDER, OBJECTIVE_ORDER.ORDER, DFS_FIRE_ARCHIVE.FIRE_MGT_ZONE, DFS_FIRE_ARCHIVE.FIRE_TYPE
HAVING (((DFS_FIRE_ARCHIVE.FIRE_YEAR)=2009) AND ((DFS_FIRE_ARCHIVE.FIRE_MGT_ZONE)="INT") AND ((DFS_FIRE_ARCHIVE.FIRE_TYPE)="IFR"))
ORDER BY GENERAL_CAUSE_ORDER.ORDER, OBJECTIVE_ORDER.ORDER, DFS_FIRE_ARCHIVE.OBJECTIVE;

问题是当我尝试在 PostgreSQL 中执行此查询时，我收到语法错误。

Answer 1

不确定 PostgreSQL，但在 Oracle 中，您可以将方括号更改为双引号。此外，它可能会抱怨，因为您根据结果集中不存在的列进行分组。您可能需要更改查询以返回所有分组依据字段，例如：

SELECT GENERAL_CAUSE_NFD."Cause Class",
       DFS_FIRE_ARCHIVE.FIRE_YEAR AS "Year",
       OBJECTIVES_NFD."Response Category",
       DFS_FIRE_ARCHIVE.GENERAL_CAUSE,
       DFS_FIRE_ARCHIVE.OBJECTIVE,
       GENERAL_CAUSE_ORDER.ORDER,
       OBJECTIVE_ORDER.ORDER,
       DFS_FIRE_ARCHIVE.FIRE_MGT_ZONE,
       DFS_FIRE_ARCHIVE.FIRE_TYPE,
       Count(DFS_FIRE_ARCHIVE.REGION) AS "Total"
  FROM (((DFS_FIRE_ARCHIVE
          INNER JOIN GENERAL_CAUSE_ORDER
            ON DFS_FIRE_ARCHIVE.GENERAL_CAUSE = GENERAL_CAUSE_ORDER.GENERAL_CAUSE)
        INNER JOIN OBJECTIVE_ORDER
          ON DFS_FIRE_ARCHIVE.OBJECTIVE = OBJECTIVE_ORDER.OBJECTIVE)
        INNER JOIN OBJECTIVES_NFD
          ON OBJECTIVE_ORDER.OBJECTIVE = OBJECTIVES_NFD.OBJECTIVE)
        INNER JOIN GENERAL_CAUSE_NFD
          ON GENERAL_CAUSE_ORDER.GENERAL_CAUSE = GENERAL_CAUSE_NFD.GENERAL_CAUSE
  GROUP BY GENERAL_CAUSE_NFD."Cause Class",
           DFS_FIRE_ARCHIVE.FIRE_YEAR,
           OBJECTIVES_NFD."Response Category",
           DFS_FIRE_ARCHIVE.GENERAL_CAUSE,
           DFS_FIRE_ARCHIVE.OBJECTIVE,
           GENERAL_CAUSE_ORDER.ORDER,
           OBJECTIVE_ORDER.ORDER,
           DFS_FIRE_ARCHIVE.FIRE_MGT_ZONE,
           DFS_FIRE_ARCHIVE.FIRE_TYPE
  HAVING (((DFS_FIRE_ARCHIVE.FIRE_YEAR)=2009) AND
          ((DFS_FIRE_ARCHIVE.FIRE_MGT_ZONE)="INT") AND
          ((DFS_FIRE_ARCHIVE.FIRE_TYPE)="IFR"))
  ORDER BY GENERAL_CAUSE_ORDER.ORDER,
           OBJECTIVE_ORDER.ORDER,
           DFS_FIRE_ARCHIVE.OBJECTIVE; 

分享并享受。

Answer 2

您的列名称都相同吗？

我面前没有 postgresql 数据库，但通用的 sql 查询看起来与此类似。请注意，我删除了内部联接周围的 () 和访问使用的 []。此外，您正在按不在查询的 SELECT 子句中的字段进行分组 - 您确定这就是您想要做的吗？

SELECT 
GENERAL_CAUSE_NFD."Cause Class", 
DFS_FIRE_ARCHIVE.FIRE_YEAR AS Year, 
OBJECTIVES_NFD."Response Category", 
Count(DFS_FIRE_ARCHIVE.REGION) AS Total

FROM 
DFS_FIRE_ARCHIVE INNER JOIN GENERAL_CAUSE_ORDER 
   ON DFS_FIRE_ARCHIVE.GENERAL_CAUSE = GENERAL_CAUSE_ORDER.GENERAL_CAUSE
INNER JOIN OBJECTIVE_ORDER 
   ON DFS_FIRE_ARCHIVE.OBJECTIVE = OBJECTIVE_ORDER.OBJECTIVE
INNER JOIN OBJECTIVES_NFD 
   ON OBJECTIVE_ORDER.OBJECTIVE = OBJECTIVES_NFD.OBJECTIVE
INNER JOIN GENERAL_CAUSE_NFD 
   ON GENERAL_CAUSE_ORDER.GENERAL_CAUSE = GENERAL_CAUSE_NFD.GENERAL_CAUSE

GROUP BY GENERAL_CAUSE_NFD."Cause Class",
DFS_FIRE_ARCHIVE.FIRE_YEAR, 
OBJECTIVES_NFD."Response Category", 
DFS_FIRE_ARCHIVE.GENERAL_CAUSE, 
DFS_FIRE_ARCHIVE.OBJECTIVE, 
GENERAL_CAUSE_ORDER.ORDER, 
OBJECTIVE_ORDER.ORDER, 
DFS_FIRE_ARCHIVE.FIRE_MGT_ZONE, 
DFS_FIRE_ARCHIVE.FIRE_TYPE

HAVING 
    DFS_FIRE_ARCHIVE.FIRE_YEAR=2009 
AND DFS_FIRE_ARCHIVE.FIRE_MGT_ZONE='INT'
AND DFS_FIRE_ARCHIVE.FIRE_TYPE='IFR'

ORDER BY GENERAL_CAUSE_ORDER."ORDER", 
         OBJECTIVE_ORDER."ORDER", 
         DFS_FIRE_ARCHIVE.OBJECTIVE;