case/if case/if语句

Question

我正在尝试在Click House中的条件下过滤数据。这是查询：

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;

如果Clickhouse中的条件，我还尝试过简单的问题，但是该子查询中的Eve不起作用。查询：

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))

是否有任何方法可以在案例内运行子征服/如果在单击房屋中？

Answer 1

您可以直接在中使用，其中子句：

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