SELECT t1.name as r_name, t1.values as r_values
FROM table as t1
JOIN (
SELECT SUM(amount) as amount
FROM database2.table
WHERE ids IN (t1.values)
) as t2
WHERE t1.id = 20;
我收到一个错误,子查询内的t1.values
是未知列。
您需要重写查询并将 innewhere
带到join
条件:
SELECT t1.name as r_name, t1.values as r_values
FROM table as t1
JOIN (
SELECT SUM(amount) as amount
FROM database2.table
) as t2 ON t2.ids = t1.values
WHERE t1.id = 20;
另外,您不使用amount
列,那么join
的意义何在?
另一个问题是,您没有定义任何join
条件。
我认为您需要首先阅读SQL中的join
:)
您似乎正在尝试根据t1.values
列表将database2.table
加入您的t1
。 自从您使用聚合功能以来,我在t2
中添加了按 ID 分组。然后,不确定您的sum(amount)
的目的是什么
SELECT t1.name as r_name, t1.values as r_values
FROM table as t1
JOIN (
SELECT SUM(amount) as amount, ids
FROM database2.table
GROUP BY ids
) as t2 on t2.ids IN (t1.values)
WHERE t1.id = 20;