You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
mysql 8.0 druid 1.2.16 springboot 2.1.8 mybatis-plus 3.1.2
问题sql语句
<insertid="overwriteData">
replace into pdd_account_order(shop_name, order_id, money, account_create_time, style_id, materials_name, materials_num, materials_money, is_divisible)
with pdd_account as (
select shop_name, order_id, create_time, income, pay, account_type
from pdd_account_excel a
where a.account_type in ('交易收入', '退款')
group by shop_name, order_id, create_time, income, pay, account_type
)
, pdd_account_res as (
select a.shop_name, a.order_id, sum(a.income + a.pay) as money, max(create_time) as create_time
from pdd_account a
group by a.shop_name, a.order_id
)
, pdd_order as (
select shop_name, order_id, num, style_id
from pdd_order_excel o
group by shop_name, order_id, num, style_id
)
, pdd_order_res as (
select o.shop_name, o.order_id, o.style_id, ifnull(m.name, '--缺少物料--') as materials_name, sum(o.num* ifnull(m.num, 0)) as materials_num
from pdd_order o
left join pdd_materials m on o.shop_name = m.shop_name and o.style_id = m.style_id
group by o.shop_name, o.order_id, o.style_id, ifnull(m.name, '--缺少物料--')
)
, pdd_order_res2 as (
select r.shop_name, r.order_id, sum(r.materials_num) as materials_num
from pdd_order_res r
group by r.shop_name, r.order_id
)
, pdd_order_res3 as (
select o.shop_name, o.order_id, o.materials_num, a.money
, a.money/nullif(o.materials_num, 0) as price
, if( MOD(a.money*10000000, nullif(o.materials_num, 0)) =0, 1, 0) as is_divisible
, a.create_time
from pdd_order_res2 o, pdd_account_res a
where o.shop_name = a.shop_name and o.order_id = a.order_id
)
select r.shop_name, r.order_id, r3.money, r3.create_time, r.style_id, r.materials_name, r.materials_num
, r.materials_num* r3.price as materials_money
, r3.is_divisible
from pdd_order_res r, pdd_order_res3 r3
where r.shop_name = r3.shop_name and r.order_id = r3.order_id
</insert>
我发现带有with语句就会报错,已经尝试了能查的方法,这是我的yml配置文件截图
The text was updated successfully, but these errors were encountered:
mysql 8.0
druid 1.2.16
springboot 2.1.8
mybatis-plus 3.1.2
问题sql语句
我发现带有
with
语句就会报错,已经尝试了能查的方法,这是我的yml配置文件截图The text was updated successfully, but these errors were encountered: