关于不支持 with (select sum(num) from table) as totalNum的问询

raxcl · 2024年04月11日 07:02

with (
SELECT sum(n_net_prm) 
from ads.policy_channel_01

) as totalNum
– select * from t
SELECT totalNum,*
from ads.policy_channel_01

报错：SQL 错误 [1064] [42000]: Getting syntax error at line 1, column 70. Detail message: No viable statement for input ‘with (’.

raxcl · 2024年04月11日 11:54

替代方案：
with totalNum as (
SELECT sum(n_net_prm)
from ads.policy_channel_01
)
SELECT (select * from totalNum) totalNum,*
from ads.policy_channel_01

yuchen1019 · 2024年04月11日 11:55

raxcl:

with (
SELECT sum(n_net_prm) 
from ads.policy_channel_01 
) as totalNum
– select * from t
SELECT totalNum,*
from ads.policy_channel_01

报错：SQL 错误 [1064] [42000]: Getting syntax error at line 1, column 70. Detail message: No viable statement for input ‘with (’.

WITH totalNum AS (
SELECT sum(n_net_prm) as total_sum
FROM ads.policy_channel_01
)
SELECT totalNum.total_sum, ads.policy_channel_01.*
FROM ads.policy_channel_01, totalNum;

raxcl · 2024年04月24日 07:29

相当于只能作为一张表，而不能作为一个特殊字段，我想是否可以参考下clickhouse的实现呢，clickhouse既可以作为字段，也可以作为表