在 Microsoft Access 中从...中选择顶部(除 10 之外的所有)

发布于 2024-07-13 04:56:23 字数 147 浏览 9 评论 0原文

有一个查询

SELECT TOP 10 ... FROM ... ORDER BY ...

假设我在 Access(实际上是 Jet)中 。 问题是:我怎样才能获得所有其他行...除了前 10 行之外的所有行?

Say I've got a query

SELECT TOP 10 ... FROM ... ORDER BY ...

in Access (well, really Jet). The question is: how can I get all the other rows... everything except the top 10?

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

扫码二维码加入Web技术交流群

发布评论

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

评论(4

清晨说晚安 2024-07-20 04:56:23

你不能做这样的事情吗?

SELECT ... FROM ...
WHERE PK NOT IN (SELECT TOP 10 PK FROM ...)
ORDER BY ...

它可能没有那么有效,但这是我能想到做这样的事情的唯一方法。 AFAIK SQL 中没有“BOTTOM”子句:)

Couldn't you do something like

SELECT ... FROM ...
WHERE PK NOT IN (SELECT TOP 10 PK FROM ...)
ORDER BY ...

it might not be that efficient but that's the only way off the top of my head I can think to do something like that. AFAIK there's no "BOTTOM" clause in SQL :)

極樂鬼 2024-07-20 04:56:23
SELECT ... FROM ....
WHERE myID NOT IN 
    (SELECT TOP 10 myID FROM ... ORDER BY rankfield)
ORDER BY sortfield

请注意,您的排序顺序可能(如果您愿意)与您的排名顺序不同。

编辑:另一个想法:如果您已经知道共有多少行,您可以这样做(假设有 1000 行):

SELECT TOP 990 ... FROM .... ORDER BY sortfield DESC

只需翻转排序,然后取剩余部分。

当然,如果你仍然想要按原始顺序排列结果,你就必须做一些愚蠢的事情,比如:

SELECT ... 
FROM (SELECT TOP 990 ... FROM .... ORDER BY sortfield DESC)
ORDER BY sortfield ASC
SELECT ... FROM ....
WHERE myID NOT IN 
    (SELECT TOP 10 myID FROM ... ORDER BY rankfield)
ORDER BY sortfield

Note that your sorted order could, (if you wish) be different than your ranked order.

Edit: Another idea: If you already knew how many TOTAL rows were there, you could do (assuming 1000 rows):

SELECT TOP 990 ... FROM .... ORDER BY sortfield DESC

Just flip the sort, and take the remaining portion.

Of course, if you still wanted the results in the original order, you'd have to do something silly like:

SELECT ... 
FROM (SELECT TOP 990 ... FROM .... ORDER BY sortfield DESC)
ORDER BY sortfield ASC
氛圍 2024-07-20 04:56:23

您可以创建一个排名字段(创建排名列的方法)并对其进行筛选:排名>10

You can create a rank field (Ways to Create Rank Column) and filter off of that: where rank >10

金兰素衣 2024-07-20 04:56:23

这通常最好在“客户端”完成,而不是在 DBMS 上完成,即将表的所有行提取到 ADO Classic 记录集中,然后使用 Filter 属性根据条件删除 10 行,或者排序然后设置然后跳过第一/最后 10 行,或设置适当的页面长度,然后跳过第一/最后一页等。它不仅取决于行数,还取决于目标应用程序,例如我知道如果这是数据源MS Access 报告然后过滤掉不需要的行可能会很麻烦。

This it something that is often better done on the 'client' side, rather on the DBMS i.e. fetch all the table's rows into an ADO Classic recordset then use the Filter property to remove the 10 rows based on criteria, or Sort then set then skip the first/last 10 rows, or set the page length as appropriate then skip the first/last page, etc. Not only does it depend on the number of rows but also the target application e.g. I know that if this is the data source for a MS Access report then filtering off the unwanted rows can be a lot of hassle.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文