动态行列转换问题?sqlserver中能执行,到access中就执行不了!郁闷
Revenue表:
accountid amount date
1 123 2004-01-11
1 232 2004-01-12
2 4023 2004-07-11
3 423 2004-07-25
4 423 2004-12-11
..
..
==
Account表:
ID Name
1 薪水
2 吃喝
3 其它收入
4 游玩
..
..
===================================
select 名称=c.Name,时间=a.月份,数量=isnull(sum(b.amount),0)
from(
select a.accountid,b.m,b.月份 from(
select accountid from revenue )a,(
select m=1 ,月份='1' union all
select m=2 ,月份='2' union all
select m=3 ,月份='3' union all
select m=4 ,月份='4' union all
select m=5 ,月份='5' union all
select m=6 ,月份='6' union all
select m=7 ,月份='7' union all
select m=8 ,月份='8' union all
select m=9 ,月份='9' union all
select m=10,月份='10' union all
select m=11,月份='11' union all
select m=12,月份='12')b
)a left join revenue b on a.accountid=b.accountid
and a.m=month(b.date)
left join account c on a.accountid=c.id group by c.id,c.Name,a.月份,a.m
order by c.ID,a.m
上面的sql语句在sql server中执行无问题,到access中,就提示:
“语法错误(操作符丢失)在查询表达式''中
====
有没有人对access的jet-sql熟悉的。