使用JOIN关联两个独立可运行的子查询SELECT语句时报语法错误如何解决

核心错误点 #

• JOIN关联的第二个子查询（生成BBB别名的查询）未使用括号包裹，SQL语法规定JOIN后接子查询时，必须用括号将完整子查询逻辑括起来，否则会触发语法解析错误。
• 最外层查询的括号范围逻辑错误，无需将整个JOIN逻辑包裹在同一个FROM后的括号内，建议将两个关联的子查询分别独立包裹为临时表后再做关联，逻辑更清晰也符合语法规范。

修正后的可运行代码 #

SELECT * FROM
(
  SELECT dt, count(*) OVER (ORDER BY dt ASC) AS cumuA 
  FROM (
    SELECT date_trunc('week',block_time) AS dt, "from" AS sender 
    FROM data WHERE "to" = 'xxxx'
  ) AS AAA
) AS t1
INNER JOIN 
(
  SELECT dt, count(*) OVER (ORDER BY dt ASC) AS cumuB 
  FROM ( 
    SELECT min(date_trunc('week',block_time)) dt, "from" AS sender 
    FROM data WHERE "to" = 'xxxx'
    GROUP BY sender
  ) AS BBB
) AS t2
ON t1.dt = t2.dt

可选优化提示 #

如果两个子查询同dt下存在多条重复记录，关联后会出现行数翻倍的情况，可根据业务需求先对两个子查询按dt做聚合去重，再执行关联操作，也可根据数据匹配需求将INNER JOIN替换为LEFT JOIN保留一侧的全量日期数据。

内容的提问来源于stack exchange，提问作者Julian