postgresql:连接2个select语句时出现连接语法错误

3duebb1j 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(338)

我正在clinicaltrials.gov上查询aact数据库。数据库模型就在这里：https://aact.ctti-clinicaltrials.org/schema. 我有两个我正在选择的模式（ctgov，proj\u cdek\u standard\u orgs）。我想加入两个select语句。编辑：我现在已经试过给我的子查询取别名，但是仍然没有效果。我得到以下错误：

(SELECT ctgov.sponsors.name, ctgov.sponsors.nct_id, ctgov.sponsors.id, ctgov.studies.phase
FROM ctgov.sponsors, ctgov.studies
WHERE ctgov.sponsors.nct_id=ctgov.studies.nct_id) A

FULL [OUTER] JOIN

(SELECT proj_cdek_standard_orgs.cdek_synonyms.id, proj_cdek_standard_orgs.cdek_synonyms.name
FROM proj_cdek_standard_orgs.cdek_synonyms) B

ON

A.name = B.name;

我自己可以很好地处理这两个select语句，但是我尝试了查询，却得到了以下错误： ERROR: syntax error at or near "t1" LINE 7: ) t1 我做错了什么？如何使用联接而不出现语法错误？

sql postgresql Join syntax

来源：https://stackoverflow.com/questions/62909300/postgresql-join-syntax-error-when-joining-2-select-statements

2条答案

按热度按时间

unhi4e5o1#

请使用下面的查询，

SELECT ctgov.sponsors.name, ctgov.sponsors.nct_id, ctgov.sponsors.id, 
    ctgov.studies.phase, proj_cdek_standard_orgs.cdek_synonyms.id, 
    proj_cdek_standard_orgs.cdek_synonyms.name
FROM ctgov.sponsors, ctgov.studies, proj_cdek_standard_orgs.cdek_synonyms
WHERE ctgov.sponsors.nct_id=ctgov.studies.nct_id
and proj_cdek_standard_orgs.cdek_synonyms.name = ctgov.sponsors.name;

但正确的方法是使用传统连接，

SELECT ctgov.sponsors.name, ctgov.sponsors.nct_id, ctgov.sponsors.id, 
    ctgov.studies.phase, proj_cdek_standard_orgs.cdek_synonyms.id, 
    proj_cdek_standard_orgs.cdek_synonyms.name
FROM ctgov.sponsors
INNER JOIN ctgov.studies
ON (ctgov.sponsors.nct_id=ctgov.studies.nct_id)
INNER  JOIN proj_cdek_standard_orgs.cdek_synonyms
ON (proj_cdek_standard_orgs.cdek_synonyms.name = ctgov.sponsors.name);

您可以根据您的要求更改为左或全外接。

赞(0）回复(0）举报 2021-07-26

acruukt92#

必须为子查询提供别名。另外，您不应该像在第一个子查询中那样使用隐式联接，始终尝试使用显式联接。

SELECT 
    *
FROM
(
  SELECT 
    ctgov.sponsors.name, ctgov.sponsors.nct_id, ctgov.sponsors.id, ctgov.studies.phase
  FROM ctgov.sponsors
  JOIN ctgov.studies
  ON ctgov.sponsors.nct_id=ctgov.studies.nct_id
) t1

FULL JOIN

(
  SELECT 
    proj_cdek_standard_orgs.cdek_synonyms.id, proj_cdek_standard_orgs.cdek_synonyms.name
  FROM proj_cdek_standard_orgs.cdek_synonyms
) t2

ON

t1.name = t2.name;

赞(0）回复(0）举报 2021-07-26

我来回答

postgresql:连接2个select语句时出现连接语法错误

2条答案

相关问题

热门标签

最新问答