导致此错误的相关子查询-如果没有联接两端的字段相等的条件，则无法使用LEFT OUTER JOIN [英] Correlated sub-query causing this error - LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join

问题描述

使用WHERE子句在BigQuery中编写相关子查询时，会导致错误.如果没有连接两边的字段相等的条件，则不能使用LEFT OUTER JOIN.

When writing correlated sub-queries in BigQuery using a WHERE clause, it is resulting in an Error. LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join.

但是它可以在其他SQL引擎中使用，但我不了解该错误或如何重写查询.

However it works in other SQL engines, I am not understanding the error or how to rewrite the queries.

相同类型的不同查询会导致相同错误.

Different queries of the same type causing the same error.

SELECT
  out.*
FROM
  `august-monolith-205810.Assignment.students` AS out
WHERE
  2 >= (
  SELECT
    COUNT(*)
  FROM
    `august-monolith-205810.Assignment.students` AS t1
  WHERE
    out.age < t1.age)

报告比其他最多两个学生大的学生.

reporting students who are older than at most two other students.

推荐答案

以下是BigQuery标准SQL

Below is for BigQuery Standard SQL

#standardSQL
SELECT * EXCEPT(cnt)
FROM (
  SELECT *, COUNT(1) OVER(ORDER BY age RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) cnt
  FROM `august-monolith-205810.Assignment.students` 
)
WHERE cnt BETWEEN 1 AND 2

另一种效果不佳，因此不建议使用，但仍然可以选择使用CROSS JOIN模仿非等距的LEFT JOIN

Another, less effective and thus not recommended but still an option is to use CROSS JOIN to mimic non-equality LEFT JOIN

#standardSQL
SELECT ANY_VALUE(out).*
FROM `august-monolith-205810.Assignment.students` AS out
CROSS JOIN `august-monolith-205810.Assignment.students` AS t1
WHERE out.age > t1.age 
GROUP BY out.id
HAVING COUNT(t1.age) <= 2

这篇关于导致此错误的相关子查询-如果没有联接两端的字段相等的条件，则无法使用LEFT OUTER JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！