search

hcymysql / sql_helper

输入SQL自动判断条件字段是否增加索引
43 stars 10 forks source link

执行计划中遇到派生表会出现错误 #8

Open k1n9met opened 1 month ago

k1n9met commented 1 month ago
image image
hcymysql commented 1 month ago

你的SQL是？我适配一下

k1n9met commented 3 weeks ago

你的SQL是？我适配一下

select d,c,a. from( select 'm' vt,'q' ar,'q' t,count() s,'300' at,'Array' m from test t left join re r on r.d=t.d where t.f >= '2009-01-01 00:00:00' and t.f < '2024-01-18 00:00:00' and t.l >= '2009-01-01 00:00:00' and t.l < '2024-01-18 00:00:00' and t.az like '0%' union all select vt,ar,t,sum(s) s,at,m from (select 'm' vt,r.cd ar,r.cd t,0 s,'300' at,'Array' m from re r where 1=1 and r.cd<>'QT' and r.cd in ('0','a2','z3','o3','04') group by r.cd union all select 'm' vt,r.cd ar,r.cd t,count() s,'300' at,'Array' m from test t left join re r on r.d=t.d where t.f >= '2009-01-01 00:00:00' and t.f < '2024-01-18 00:00:00' and t.l >= '2009-01-01 00:00:00' and t.l < '2024-01-18 00:00:00' and r.cd<>'QT' and t.az like '0%' and r.cd in ('0','a2','z3','o3','04') group by r.title union all select 'm' vt,r.d ar,r.cd t,0 s,'300' at,'Array' m from re r where 1=1 and r.dn is not null and r.d in ('0','a2','z3','o3','04') group by r.d,r.cd union all select 'm' vt,r.d ar,r.cd t,count() s,'300' at,'Array' m from test t left join re r on r.d=t.d where t.f >= '2009-01-01 00:00:00' and t.f < '2024-01-18 00:00:00' and t.l >= '2009-01-01 00:00:00' and t.l < '2024-01-18 00:00:00' and r.cd<>'QT' and t.az like '0%' and r.dn is not null and r.d in ('0','a2','z3','o3','04') group by r.d,r.cd) u group by vt,ar,t,at,m) a left join at b on b.cd=a.at where 1=1 and b.cd='300';

k1n9met commented 3 weeks ago

之前那个sql不记得是哪个了，我随便找了另外一个也会复现的sql

image image
hcymysql commented 3 weeks ago

链接：https://pan.baidu.com/s/13sXKIiFdWz-ANt9jS0oFOw 提取码：7i6d

下载最新版的再试试。

k1n9met commented 3 weeks ago

链接：https://pan.baidu.com/s/13sXKIiFdWz-ANt9jS0oFOw 提取码：7i6d

下载最新版的再试试。

运行没问题了，谢谢