SQL CE中子查询作为计算列使用怎么查询

[usun82]

表GunSales结构为：
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, --主键(自增)
[BeginAmount] [decimal](18, 2) NULL, --开始数
[EndAmount] [decimal](18, 2) NULL, --截止数
[SalesAmount] [decimal](18, 2) NULL, --销量
[SalesNO] [int] NULL, --销售记录号(每笔销售自增)
表GunSales数据为：
ID BeginAmount EndAmount SalesAmount SalesNO
1 0.00 10.00 10.00 1
2 10.00 20.00 10.00 2
3 30.00 50.00 20.00 4
4 60.00 90.00 30.00 6
查询的语句为：
select (select top (1) EndAmount from GunSales where SalesNO<a.SalesNO order by SalesNO desc) as BeginAmount
from GunSales a
where a.EndAmount-a.SalesAmount <> (select top (1) EndAmount from GunSales where SalesNO<a.SalesNO order by SalesNO desc)
order by a.SalesNO ASC
这个语句在SQL Server下查询结果是对的，因为SQL CE不执行含top的子查询，请问：和这个语句等同的查询写法这么写？先感谢了！！！

[usun82]

是用SqlCeResultSet游标吗

[發糞塗牆]

引用 6 楼 usun82 的回复:

是用SqlCeResultSet游标吗

对

[--小F--]

SQL CE不支持TOP 所以你这里的TOP 可以直接用MAX代替

[--小F--]

select  (select MAX(EndAmount) from GunSales where SalesNO<a.SalesNO order by SalesNO desc) as BeginAmount
 from GunSales a
 where a.EndAmount-a.SalesAmount <> (select MAX(EndAmount) from GunSales where SalesNO<a.SalesNO order by SalesNO desc) 
 order by a.SalesNO ASC

[發糞塗牆]

用游标读取前N行

[usun82]

我这样改了： select (select MAX(EndAmount) from GunSales where SalesNO<a.SalesNO) as BeginAmount from GunSales a where a.EndAmount-a.SalesAmount <> (select MAX(EndAmount) from GunSales where SalesNO<a.SalesNO) order by a.SalesNO ASC 在SQL CE下还是执行不了，SQL Server下查询结果是对的。 SQL CE好像不支持这种子查询的写法，能不能把这个语句用join连接查询或其他的方式来写呢？SQL CE支持游标吗？

[發糞塗牆]

我在google上查，说的不是直接调用sql语句，而是用编程语言中的游标来读取