英文:
MS ACCESS Error in insert into X(...) select ... from A union select ... from B
问题
Access 2000 (mdb base).
Stametement:
insert into full_join_DR(y, m, ds, rs, b)
select r.y, r.m, d.summa as ds, r.summa as rs, d.summa - r.summa as b
from r left join d on r.y = d.y and r.m = d.m
union
select d.y, d.m, d.summa, r.summa, d.summa - r.summa
from d left join r on r.y = d.y and r.m = d.m
> 错误：语法错误
尽管内部的选择语句工作正常
英文:
Access 2000 (mdb base).
Stametement:
insert into full_join_DR(y,m,ds,rs,b)
select r.y, r.m, d.summa as ds , r.summa as rs , d.summa - r.summa as b
from r left join d on r.y=d.y and r.m=d.m
union
select d.y, d.m, d.summa, r.summa , d.summa - r.summa
from d left join r on r.y=d.y and r.m=d.m
> Error:Syntax error
though inner select statement works correctly
答案1
得分: 0
尝试将联合查询用作子查询：
insert into full_join_DR(y,m,ds,rs,b)
select t.y, t.m, t.ds, t.rs, t.b
from
(select r.y, r.m, d.summa as ds, r.summa as rs, d.summa - r.summa as b
from r left join d on r.y=d.y and r.m=d.m
union
select d.y, d.m, d.summa, r.summa, d.summa - r.summa
from d left join r on r.y=d.y and r.m=d.m) as t
英文:
Try using the union query as a subquery:
insert into full_join_DR(y,m,ds,rs,b)
select t.y, t.m, t.ds, t.rs, t.b
from
(select r.y, r.m, d.summa as ds, r.summa as rs, d.summa - r.summa as b
from r left join d on r.y=d.y and r.m=d.m
union
select d.y, d.m, d.summa, r.summa, d.summa - r.summa
from d left join r on r.y=d.y and r.m=d.m) as t
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论