水到渠成  发表于 2020-5-5 11:00 显示全部楼层 楼主
with as 怎么能用
他是个疯子  发表于 2020-5-5 12:44 显示全部楼层 沙发
需要打开_t_gcluster_support_cte 参数,gbase8a集群才能支持with as语句。
从手册上沾了了sql样例:
with customer_total_return as
(select sr_customer_sk as ctr_customer_sk,
         sr_store_sk as ctr_store_sk,
         sum(SR_STORE_CREDIT) as ctr_total_return
    from store_returns, date_dim
   where sr_returned_date_sk = d_date_sk
     and d_year = 2000
   group by sr_customer_sk, sr_store_sk)
select c_customer_id
  from customer_total_return ctr1, store, customer
where ctr1.ctr_total_return >
       (select avg(ctr_total_return) * 1.2
          from customer_total_return ctr2
         where ctr1.ctr_store_sk = ctr2.ctr_store_sk)
   and s_store_sk = ctr1.ctr_store_sk
   and s_state = 'MI'
   and ctr1.ctr_customer_sk = c_customer_sk
order by c_customer_id limit 100;
您需要登录后才可以回帖 登录 | 立即注册