Question

ASP+SQL中sql查询语句的问题

select * from (select top 5 * from (select top 10 * from 建材新闻 where newsbgid=2 and ok=1 and tuijian=1 order by id desc) where newsbgid=2 and tuijian=1 and ok=1 order by id asc) order by id desc

这条查询语句在ACCESS的数据库中就没问题,可是换SQL的数据库就不行了,在SQL查询分析器里查询,也出错,显示:
服务器: 消息 156，级别 15，状态 1，行 1
在关键字 'where' 附近有语法错误。
请问这条应该怎么修改??

Answer 1

select * from 建材新闻 where EXISTS (select top 5 * from 建材新闻 where EXISTS (select top 10 * from 建材新闻 where newsbgid=2 and ok=1 and tuijian=1 order by id desc) and newsbgid=2 and tuijian=1 and ok=1 order by id asc) order by id desc

不过这段代码可以优化，最起码可以写作
select * from 建材新闻 where EXISTS (select top 5 * from 建材新闻 where EXISTS (select top 10 * from 建材新闻 where newsbgid=2 and ok=1 and tuijian=1 order by id desc) order by id asc) order by id desc

Answer 2

select * from (select top 5 * from (select top 10 * from 建材新闻 where newsbgid='2' and ok='1' and tuijian='1' order by id desc) where newsbgid='2' and tuijian='1' and ok='1' order by id asc) order by id desc