活动专区

活动公告

上车！GBase 8a开发者联盟发车了 技术福利、人脉资源全都有！

认证培训专区

认证培训资料

GBase 8s GDCA 认证培训课前准备

GBase 8a

其他

物化视图报错：references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

01-GBase 8s 事务型数据库 安装与卸载

GBase 8s

适配迁移

[GBase 8s 教程]GBase 8s 常用函数、表达式

<h2>case when子查询</h2>gbase 8a使用case when子查询时如果子查询中关联其他表的数据需要进行修改成支持的with as语法，否则会报错：基础数据：create database jilin; drop table if exists t1; drop table if exists t2; drop table if exists t3; create table t1(c1 int,c2 varchar(20)); create table t2(c1 int,c2 varchar(20)); create table t3(c1 int,c2 varchar(20)); ​ insert into t1 values(1,1),(2,2),(3,3); insert into t2 values(1,1),(2,2),(3,3),(4,4); insert into t3 values(1,1),(2,2),(3,3),(5,5);<h2>示例</h2>mysqlselect t3.c1,t3.c2, case when not exists (select 1 from t1 where t1.c1=t2.c1) then sum(t2.c1) else 0 end from t2 left join t3 on t2.c1=t3.c1 group by t3.c1,t3.c2,t2.c1 order by t3.c1;结果：<img src="https://g1.cdn.gbase.cn/CommunityUploads/x8PFjn3ola4lsm0hI8MYE/image.png">gbase执行报错gbase&gt; select t3.c1,t3.c2, &nbsp;&nbsp; &nbsp;-&gt; case when not exists (select 1 from t1 where t1.c1=t2.c1) then sum(t2.c1) else 0 end &nbsp;&nbsp; &nbsp;-&gt; from t2 &nbsp;&nbsp; &nbsp;-&gt; left join t3 on t2.c1=t3.c1 &nbsp;&nbsp; &nbsp;-&gt; group by t3.c1,t3.c2,t2.c1 &nbsp;&nbsp; &nbsp;-&gt; order by t3.c1; ERROR 1708 (HY000): [192.168.58.116:5050](GBA-02AD-0005)Failed to query in gnode: DETAIL: (GBA-01EX-700) The query includes syntax that is not supported by the Express engine. Either restructure the query with supported syntax, or enable the GBase Query Path in the configuration file to execute the query with reduced performance. SQL: CREATE TABLE `gctmpdb`._tmp_1933224128_14_t11_4_1745996978_s AS SELECT /*192.168.58.115_14_44_2025-04-30_17:07:02*/ /*+ TID('14286927') */ `_tmp_rht_1933224128_14_t11_3_1745996978_s_n1`.`c5` AS&nbsp;gbase通过with as语法兼容-- with as语法需要打开参数_t_gcluster_support_cte set _t_gcluster_support_cte=1; with temp1 as(select * from t2 where not exists(select 1 from t1 where t1.c1=t2.c1)), temp2 as(select * from t2 where exists(select 1 from t1 where t1.c1=t2.c1)) (select t3.c1,t3.c2,sum(te.c1) from temp1 te left join t3 on t3.c1=te.c1 group by t3.c1,t3.c2 order by t3.c1 asc) union all&nbsp; (select t3.c1,t3.c2,0 from temp2 te left join t3 on t3.c1=te.c1 group by t3.c1,t3.c2 order by t3.c1 asc);结果：<img src="https://g1.cdn.gbase.cn/CommunityUploads/8Jdt-J5542XWXN8PVfsqE/image.png">注：另外union all如果排序需要加上括号，否则会报错：<h2>错误的示例</h2>不能直接将关联的外表加入子查询不能直接将关联的外表加入子查询，虽然sql可以运行，但sum没有生效-- 错误的示例 select t3.c1,t3.c2, case when not exists (select 1 from t1,t2 where t1.c1=t2.c1) then sum(t2.c1) else 0 end as 'casewhen' from t2 left join t3 on t2.c1=t3.c1 group by t3.c1,t3.c2,t2.c1 order by t3.c1; +------+------+----------+ | c1 &nbsp; | c2 &nbsp; | casewhen | +------+------+----------+ | &nbsp; &nbsp;1 | 1 &nbsp; &nbsp;| &nbsp; &nbsp; &nbsp; &nbsp;0 | | &nbsp; &nbsp;2 | 2 &nbsp; &nbsp;| &nbsp; &nbsp; &nbsp; &nbsp;0 | | &nbsp; &nbsp;3 | 3 &nbsp; &nbsp;| &nbsp; &nbsp; &nbsp; &nbsp;0 | | NULL | NULL | &nbsp; &nbsp; &nbsp; &nbsp;0 | +------+------+----------+ 4 rows in set (Elapsed: 00:00:00.22) ​ gbase&gt;&nbsp; &nbsp;

兼容mysql之case when子查询

兼容mysql之case when子查询

评论

热门帖子