当前位置：文江博客话题详情

SQL Server 2000 对具有三个键的表进行分页

发布于 2024-08-02 00:08:48 字数 376 浏览 10 评论 0原文

几天来我一直在尝试解决这个问题，但运气不佳。我发现了大量讨论 SQL Server 2000 上分页的资源此处和codeproject。

我面临的问题是尝试在一个表上实现某种分页机制，该表具有构成主键的三个键。操作员、客户标识符、断开连接日期。

任何帮助/指示将不胜感激

原文

分享到QQ

分享到微博

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

发布评论

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

黑凤梨 2024-08-09 00:08:48

SQL Server 2000 没有方便的 row_number 函数，因此您必须使用子查询自动生成行号列，如下所示：

select
    *
from
    (select
        *,
        (select count(*) from tblA where 
            operator < a.operator
            or (operator = a.operator
                and customeridentifier < a.customeridentifier)
            or (operator = a.operator
                and customeridentifier = a.customeridentifier
                and datedisconnected <= a.datedisconnected)) as rownum
     from
        tblA a) s
where
    s.rownum between 5 and 10
order by s.rownum

但是，您可以按中的任何列对这些行进行排序表——它不必使用组合键。它可能也会跑得更快！

此外，复合键通常是一个标志。您不只是使用对这三列具有唯一约束的代理键，是否有任何特殊原因？

SQL Server 2000 doesn't have the handy row_number function, so you'll have to auto-generate a row number column with a subquery, like so:

select
    *
from
    (select
        *,
        (select count(*) from tblA where 
            operator < a.operator
            or (operator = a.operator
                and customeridentifier < a.customeridentifier)
            or (operator = a.operator
                and customeridentifier = a.customeridentifier
                and datedisconnected <= a.datedisconnected)) as rownum
     from
        tblA a) s
where
    s.rownum between 5 and 10
order by s.rownum

However, you can sort those rows by any column in the table -- it doesn't have to use the composite key. It would probably run faster, too!

Additionally, composite keys are usually a flag. Is there any particular reason you aren't just using a surrogate key with a unique constraint on these three columns?

回复收藏 0 原文

~没有更多了~