在 Sybase ASE 中查找第 n 行? [英] Finding the n-th row in Sybase ASE?

问题描述

我正在尝试查找 sybase 数据库中的第 n 行.我更熟悉 SQL 服务器，所以我决定使用 with 语句，但由于某种原因，它在 sybase 中不起作用.请你们解释一下这段代码有什么问题:

I'm trying to find the n-th row in a sybase database. I'm more familiar with SQL server so I decided to use a with statement but for some reason that's not working in sybase. Could you guys please explain what's wrong with this code:

With test AS 
(
  select *, row_number() over (order by M_MAT) as 'row'
  from OM_MAT_DBF
) 
SELECT *
FROM test
WHERE row = 2

推荐答案

with 和 row_number() 在 Sybase ASE 中不是有效命令.

with and row_number() are not valid commands in Sybase ASE.

一种选择是将您的数据(或关键数据)选择到一个临时表中，然后使用它来查找您要查找的行.

One option is to select your data (or key data) into a temp table, then use that to find the rows you are looking for.

set rowcount 13      --Use the row number you are looking for to limit rows returned
select rownumber=identity(10), M_MAT
  into #temp
  from OM_MAT_DBF
  order by M_MAT
set rowcount 0

这将创建带有行号的临时表.假设 M_MAT 是一个唯一字段:

This will create temporary table with rownumbers. Assuming M_MAT is a unique field:

select * 
  from OM_MAT_DBF
  where M_MAT =
    (
    select M_MAT 
    from #temp where rownumber = 13   --And find your requested row
    )

如果您计划在一个事务中选择多个行，那么您可以在创建临时表时始终绕过 set rowcount 命令.

If you plan on selecting more than one row in a transaction, then you can always bypass the set rowcount command when creating the temp table.

这篇关于在 Sybase ASE 中查找第 n 行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！