sql server 2000：获取前 N 行

发布于 2024-11-18 16:04:59 字数 467 浏览 1 评论 0原文

我正在运行一个简单的查询，获取某个表的前 5000 行：

  SELECT TOP 5000 
         accountid, account, accountmanagerid, mainphone, alternatephone, fax, email, webaddress, createuser, modifyuser, createdate, modifydate, addressid, shippingid 
    FROM sysdba.account 
   WHERE 1 = 1 
     AND 1 = 1 
ORDER BY accountid asc

这在 sql server 2008 上完美运行，但不幸的是现在我发现自己试图针对 SQL Server 2000 运行这个以前工作的脚本。它似乎拒绝了 top 命令。

我应该怎么做才能取回此版本的 sql server 上的 5000 行？

原文

I'm running a simple query that gets the top 5000 rows of some table:

  SELECT TOP 5000 
         accountid, account, accountmanagerid, mainphone, alternatephone, fax, email, webaddress, createuser, modifyuser, createdate, modifydate, addressid, shippingid 
    FROM sysdba.account 
   WHERE 1 = 1 
     AND 1 = 1 
ORDER BY accountid asc

This perfectly worked on sql server 2008, but unfortunately now I find myself trying to run this formerly working script against SQL Server 2000. It seems to reject the top command.

What should I do to get back my 5000 rows on this version of the sql server?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

错々过的事 2024-11-25 16:05:00

TOP 关键字在 SQL Server 2000 中不可用。但是您可以使用 rowcount 来过滤排名靠前的记录。您可以将上面的查询修改如下：


-- 1=1 is NOT needed unless you are appending this string as dynamic SQL

SET ROWCOUNT 50
SELECT 
         accountid, account, accountmanagerid, mainphone, alternatephone, fax, email, webaddress, createuser, modifyuser, createdate, modifydate, addressid, shippingid 
    FROM sysdba.account 
ORDER BY accountid asc

TOP keyword isnt available in SQL Server 2000. However you can use rowcount to filter top records. You can modify the above query as below:


-- 1=1 is NOT needed unless you are appending this string as dynamic SQL

SET ROWCOUNT 50
SELECT 
         accountid, account, accountmanagerid, mainphone, alternatephone, fax, email, webaddress, createuser, modifyuser, createdate, modifydate, addressid, shippingid 
    FROM sysdba.account 
ORDER BY accountid asc

回复收藏 0 原文

世态炎凉 2024-11-25 16:05:00

没有热门关键字，您也可以获得此问题的解决方案，如下所示-

select * from 
(
  select t_1.*,rownum c_1 from 
  (
  select accountid,account,accountmanagerid,mainphone,alternatephone,
       fax,email,webaddress,createuser, modifyuser,createdate,modifydate,addressid,hippingid
  FROM sysdba.account     
  WHERE 1 = 1-- whatever condition
    AND 1 = 1 -- condition 2 
  ORDER BY accountid asc 
  )t_1
)
where c_1 <5000
order by c_1

without TOP keywords also you can get solution for this question as follow-

select * from 
(
  select t_1.*,rownum c_1 from 
  (
  select accountid,account,accountmanagerid,mainphone,alternatephone,
       fax,email,webaddress,createuser, modifyuser,createdate,modifydate,addressid,hippingid
  FROM sysdba.account     
  WHERE 1 = 1-- whatever condition
    AND 1 = 1 -- condition 2 
  ORDER BY accountid asc 
  )t_1
)
where c_1 <5000
order by c_1

回复收藏 0 原文

~没有更多了~