Clickhouse子查询不允许使用CASE/IF语句

Question

我试图在点击房子条件的帮助下过滤数据。这是查询:

SELECT CASE
WHEN EXISTS (
SELECT * FROM (SELECT Time, Lon, Users FROM table1 ORDER BY Time DESC LIMIT 5) WHERE Users>=75
)
THEN ( * FROM (SELECT Time, Lon, Users FROM table1 ORDER BY Time DESC LIMIT 5) WHERE Users<75)
ELSE ( * WHERE 0=1)
END;

我也尝试过简单的IF条件在clickhouse，但即使在子查询没有工作。查询:

SELECT if((SELECT count() FROM table1 WHERE Users>=75)>0, SELECT * FROM table1 WHERE Users<75, plus(2, 6));

两次的基本错误都是一样的:

Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 77 ('default'): default.4g WHERE Users<75, plus(2, 6));
. Expected one of: LIKE, GLOBAL NOT IN, end of query, AS, DIV, IS, OR, QuestionMark, BETWEEN, NOT LIKE, MOD, AND, Comma, alias, IN, ILIKE, Dot, NOT ILIKE, NOT, token, NOT IN, GLOBAL IN (version 21.3.20.1 (official build))

是否有办法在CASE/IF点击房子内运行子查询?

Answer 1

您可以在WHERE子句中直接使用EXISTS:

SELECT Time, Lon, Users 
FROM table1 
WHERE Users < 75
AND EXISTS (SELECT * FROM table1 WHERE Users >= 75)
ORDER BY Time DESC LIMIT 5;