select a.用户编号 as 用户编号,a.用户名称 as 用户名称,a.区域 as 区域 ,
a.单价 as 单价, isnull(b.本期指数,0) as 本期指数 from yonghu a left join yongshui b on a.用户编号 =b.用户编码 and not exists(select 1 from yongshui t where t.用户编码=b.用户编码 and t.录入日期>b.录入日期)
大家好啊,上面这个语句在sqlserver中可以通过,但在access中就不行,那我怎么写能表达上门的意思,同时又能在access中运行啊,拜托啦,给分的,呵呵
a.单价 as 单价, isnull(b.本期指数,0) as 本期指数 from yonghu a left join yongshui b on a.用户编号 =b.用户编码 and not exists(select 1 from yongshui t where t.用户编码=b.用户编码 and t.录入日期>b.录入日期)
大家好啊,上面这个语句在sqlserver中可以通过,但在access中就不行,那我怎么写能表达上门的意思,同时又能在access中运行啊,拜托啦,给分的,呵呵
select a.用户编号 as 用户编号,a.用户名称 as 用户名称,a.区域 as 区域 ,
a.单价 as 单价, iif(isnull(b.本期指数),0,本期指数) as 本期指数1 from yonghu a left join yongshui b on a.用户编号 =b.用户编码 and not exists(select 1 from yongshui t where t.用户编码=b.用户编码 and t.录入日期>b.录入日期)
select c.用户编号, c.用户名称, c.区域, c.单价, IIf(isnull(d.本期指数,), 0, d.本期指数)
from (
select a.用户编号, a.用户名称, a.区域, a.单价, b.录入日期
from yonghu a
left join (select id, max(录入日期) as 录入日期 FROM yongshui GROUP BY id) as b
on b.id = a.id
) as c
left join yongshui d on (d.id = c.id) and (d.录入日期= c.录入日期);1、未完全测试,供参考
2、多层嵌套,效率不高,大数量慎用
iif(isnull(b.本期指数), 0, b.本期指数)