“必须聚合相关标量子查询”尽管聚合了相关标量子查询仍然出错？ - 火花SQL

Question

我有一个类似于以下内容的查询：

SELECT (SELECT first(b.id) FROM table_b b WHERE b.person = a.student LIMIT 1) student_id,
       (SELECT first(b.id) FROM table_b b WHERE b.person = a.teacher LIMIT 1) teacher_id,
       'additional_field' AS additional_field
FROM table_a  a

这会产生以下错误：

The SQL expression for node [ SQLNode7 ] is invalid. Reason: [ Correlated scalar subqueries must be aggregated: GlobalLimit 1

请注意，此查询在 Redshift 中运行得非常好（没有 first()）

最初，我没有添加 first() 聚合，但我在收到此错误后这样做了。但是添加后这个错误仍然存​​在。

我尝试过的其他一些事情：

• 使用 max() 而不是 first()：
• 尝试 max(first()) 时出现同样的错误：错误说明原因：[不允许在另一个聚合函数的参数中使用聚合函数。请在子查询中使用内部聚合函数。

我是否没有正确聚合此查询？我还可以尝试根据此错误“聚合”我的查询吗？

---

最小可重现示例：

输入表：

table_a 
student teacher
A       Z
B       Z
C       Z
    
    
table_b 
id  person
1   A
2   B
3   C
4   Z  

输出：

table_c 
student_id  teacher_id
1           4
2           4
3           4  

Answer 1

它不理解 LIMIT 1 的用法：删除它并将子查询包装在聚合函数中 - MIN() 看起来是一个不错的选择 - 添加必需的 <代码>分组依据

SELECT
 (SELECT MIN(b.id) FROM table_b b WHERE b.person = a.student) student_id,
 (SELECT MIN(b.id) FROM table_b b WHERE b.person = a.teacher) teacher_id,
  'additional_field' AS additional_field
FROM table_a a
GROUP BY 3