我正在使用mysql workbench 8.0。我正在尝试为下面正在工作的查询实现一个嵌套查询。我试图避免使用createtable命令。但是,它显示错误。(对于此服务器版本,select在此位置无效,应为“(”,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;
请帮忙我做错什么了。
谢谢
1条答案
按热度按时间9w11ddsr1#
你可以像下面这样试试
从查询中去掉分号