用SQL怎么写？

applepig

如何用sql语句写：只取符合条件记录的最后一条？
例如：符合条件的有10条，排序后只取最后一条。

daliwa

你可以用olap函数中的row_number ()对记录进行编号,取最大的编号记录就是了.

详情请参考精典文章:
http://www-128.ibm.com/developerworks/cn/db2/library/techarticles/0110lyle/0110lyle.html

applepig

举例说明好不，我很笨那，

daliwa

select row_number() over (order by salary )as rownumber,id,name
from staff order by rownumber desc fetch first 1 row only;

应该可以满足你的要求.

faceDragon

原帖由 "daliwa" 发表：
select row_number() over (order by salary )as rownumber,id,name
from staff order by rownumber desc fetch first 1 row only;

应该可以满足你的要求.

如果用row_number，就不需要用fetch子句。加上条件where rownumber=n就可以了。
如果用fetch，就不需要用row_number。
觉得一般不会按行号排序的。

applepig

在delphi中写sql：

with temp1 (occurtime,ucardnum,amount,consume) as (select max(occurtime) as occurtime,ucardnum,amount,consume from consumetable where occurtime>;'2005-05-01' and occurtime<'2005-05-25' and ucardnum in (select userid from userinfotable) group by ucardnum) select * from temp1


总报错：incorrect syntax near the keyword 'with'


不知道怎么回事？？？

faceDragon

可能不支持这样写吧。
那就用查询嵌套好了。

daliwa

呵呵,杀鸡用牛刀了
(为什么偶总喜欢用复杂的方法解决问题呢,笨死!)

daliwa

原帖由 "applepig" 发表：
在delphi中写sql：

with temp1 (occurtime,ucardnum,amount,consume) as (select max(occurtime) as occurtime,ucardnum,amount,consume from consumetable where occurtime>;'2005-05-01' and occurtime<'2..........

你的SQL明显有错:
select max(occurtime) as occurtime,ucardnum,amount,consume from consumetable where occurtime>;'2005-05-01' and occurtime<'2005-05-25' and ucardnum in (select userid from userinfotable) group by ucardnum)

你在select 中和group by 中指定的列数不相同
只能group by ucardnum,amount,consume .