无法在子句中使用比较运算符?
[如何再现]
- ClickHouse服务器版本:V19.11.0
- 带有两个表的示例
table1
CREATE TABLE table1
(
str1 String,
num1 UInt64
) ENGINE=MergeTree()
ORDER BY (str1);
table2
CREATE TABLE table2
(
str2 String,
num2 UInt64
) ENGINE=MergeTree()
ORDER BY (str2);
查询
SELECT
*
FROM
table1 AS t1
LEFT OUTER JOIN
table2 AS t2
ON
t1.str1 = t2.str2
AND greater(t1.num1, t2.num2) = 1;
错误消息
DB::Exception: Invalid columns in JOIN ON section. Columns num1 and num2 are from different tables..
没有AND greater(t1.num1, t2.num2) = 1
,查询成功。
我认为您需要ASOF加入。
当您需要加入没有确切的记录时,ASOF加入很有用匹配。
例如,从table_1中选择count((asof左键加入表_2 on table_1.a == table_2.b and table_2.t< = table_1.t。
https://clickhouse.com/docs/en/sql-reference/statement/select/join/#asof-join-usage