尝试嵌套查询时,MYSQL Workbench错误 [英] MYSQL Workbench Error while trying nested query
本文介绍了尝试嵌套查询时,MYSQL Workbench错误的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用MySQL Workbench 8.0.我正在尝试为正在运行的以下查询实现嵌套查询.我试图避免创建表命令.但是,它显示错误.(Select在此服务器版本的此位置上无效,期望为'(',带有)
I am using MySQL workbench 8.0 . I am trying to implement a nested query for the following query which is working. I am trying to avoid the create table command. However it is showing the error.( Select is not valid at this position for this server version, expecting '(', with )
可执行查询是
create table recent as
select f.member_id,f.fill_date,f.insurancepaid,
p.member_first_name,p.member_last_name,d.drug_name,
row_number() over (partition by p.member_id order by f.fill_date desc) as flag
from fact_member_drug f
left join dimension_drug_name p
on f.member_id = p.member_id
left join dim_drugdetail_table d
on f.drug_ndc = d.drug_ndc;
select member_id,member_first_name,member_last_name,drug_name,fill_date,insurancepaid
from recent where flag = 1;
我写的查询是
select sub.member_id,sub.member_first_name,sub.member_last_name,sub.drug_name,sub.fill_date,sub.insurancepaid
from (
select f.member_id,f.fill_date,f.insurancepaid,
p.member_first_name,p.member_last_name,d.drug_name,
row_number() over (partition by p.member_id order by f.fill_date desc) as flag
from fact_member_drug f
left join dimension_member p
on f.member_id = p.member_id
left join dimension_drug_name d
on f.drug_ndc = d.drug_ndc;
) as sub
where sub.flag = 1;
请帮助我做错了事.
谢谢
推荐答案
您可以尝试以下操作
with cte as
(
select f.member_id,f.fill_date,f.insurancepaid,
p.member_first_name,p.member_last_name,d.drug_name,
row_number() over (partition by p.member_id order by f.fill_date desc) as flag
from fact_member_drug f
left join dimension_drug_name p
on f.member_id = p.member_id
left join dim_drugdetail_table d
on f.drug_ndc = d.drug_ndc
) select * from cte where flag=1
从查询中删除分号
select sub.member_id,sub.member_first_name,sub.member_last_name,sub.drug_name,sub.fill_date,sub.insurancepaid
from (
select f.member_id,f.fill_date,f.insurancepaid,
p.member_first_name,p.member_last_name,d.drug_name,
row_number() over (partition by p.member_id order by f.fill_date desc) as flag
from fact_member_drug f
left join dimension_member p
on f.member_id = p.member_id
left join dimension_drug_name d
on f.drug_ndc = d.drug_ndc // just remove semicolon
) as sub
where sub.flag = 1
这篇关于尝试嵌套查询时,MYSQL Workbench错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文