Question

在SQL里面运行正常，可是在ACCESS里面总是报语法错误，请问到底那里错了

select L.* from
(select * from [Web_Patient] as op1
where not exists(select 1 from [Web_Patient] as ip1 where ip1.[UserName]=op1.[Username] and ip1.[id]>op1.[id])) as L
join (select * from [Web_Patient] as op2 where
(select count(*) from [Web_Patient] as ip2 where ip2.[UserName]=op2.[Username]
and ip2.[id]>op2.[id])=1) as R on L.[UserName]=R.[UserName]
select L.* from (select * from #Web_Patient as op1 where
not exists(select 1 from #Web_Patient as ip1 where ip1.UserName=op1.Username and ip1.id>op1.id)) as L
join
(select * from #Web_Patient as op2 where
(select count(*) from #Web_Patient as ip2 where ip2.UserName=op2.Username and ip2.id>op2.id)=1) as R
on L.UserName=R.UserName
where (case when L.ALT<=10 then 10-L.ALT
when L.ALT>=50 then L.ALT-50
else 0
end)>
(case when R.ALT<=10 then 10-R.ALT
when R.ALT>=50 then R.ALT-50
else 0
end)
目的：
查询 “最后一次的结果比第一次好”

注解：‘ALT’ 肝脏检查里面的一个数据，比如正常人的 ALT值，应该是 10—50之间；

举例，如果‘王三’ 最近一次的查询的ALT值是65，而他上一次查询的ALT值是55，说明王三的ALT值恶化了。

需求，查询条件是，最近一次和上一次比较ALT恶化的患者数据

Answer 1

这条sql有什么作用?
我试过了，在Sql Server里面执行是没有问题的。
Access对sql的语句支持是有限的。
不支持多层次嵌套的查询。
特别是这一句:
where (select count(*) from Web_Patient as ip2 where p2.UserName=op2.Username and ip2.id>op2.id)=1
你现在一张表Web_Patient ，两个字段（Username，id）
知道查询目的，重写一个就好了。

如不能解决问题，发百度消息给我。

追问

--,不行where (select count(*) from Web_Patient as ip2 where p2.UserName=op2.Username and ip2.id>op2.id)=1
这句是用来，这一次的值，与上一次值来比较的

追答

才“一张表Web_Patient ，两个字段（Username，id）”你想查询得到什么结果。给另外一种解决方案。Access不支持那么复杂的查询。

明白你的意思了。。刚才分三步改写了一下sql：

create table Web_Patient(id int,ALT int,username varchar(16));

insert into Web_Patient(id,ALT,username) values (1,1,'a');
insert into Web_Patient(id,ALT,username) values (2,2,'b');
insert into Web_Patient(id,ALT,username) values (3,3,'a');
insert into Web_Patient(id,ALT,username) values (4,3,'b');
insert into Web_Patient(id,ALT,username) values (5,7,'a');
insert into Web_Patient(id,ALT,username) values (6,8,'c');

--得到每位病人最后一次诊查报告
select * from Web_Patient where id in (select max(id) as max_id from Web_Patient group by username)

--得到倒数第二次报告
select * from Web_Patient where id in (select max(id) as max_id from Web_Patient where id not in (select max(id) as max_id from Web_Patient group by username) group by username)

--用“用户名”为主键连接两次表，查询状态恶化了的(即查询 最后一次的alt>倒数第二次的alt)
select L1.*,L2.ALT as 上一次报告Alt值 from
(select * from Web_Patient where id in (select max(id) as max_id from Web_Patient group by username)) L1
left join
(select * from Web_Patient where id in (select max(id) as max_id from Web_Patient where id not in (select max(id) as max_id from Web_Patient group by username) group by username)) L2
on L1.username=L2.username
where L1.alt>L2.alt

--加上一个条件，排除最后一次检查恢复正常值水平的报告。。
select L1.*,L2.ALT as 上一次报告Alt值 from
(select * from Web_Patient where id in (select max(id) as max_id from Web_Patient group by username)) L1
left join
(select * from Web_Patient where id in (select max(id) as max_id from Web_Patient where id not in (select max(id) as max_id from Web_Patient group by username) group by username)) L2
on L1.username=L2.username
where L1.alt>L2.alt and L1.alt not between 10 and 50

另外，Access中没有Case When，只有IIF()函数。

Answer 2

缺'这个符号

追问

麻烦您说清楚点，什么地方少 ' 这个符号了