如何查询SQL数据库以返回第一个“页面”包含指定的数据？ [英] How do I query a SQL database to return the first "page" containing the specified data?

问题描述

  ------------------------ 
 | | A | | 
 ------------------------ 
 | | B | | 
 ------------------------ 
 | | C | | 
 ------------------------ -----第1页
 | | D | | 
 ------------------------ 
 | | E | | 
 ------------------------ 
 | | F | | 
 ------------------------ -----第2页
 | | F | | 
 ------------------------ 
 | | F | | 
 ------------------------ 
 | | G | | ----- Page 3 
  

请原谅我的ascii艺术，从来没有做过。 >

我有一个数据库，第二个colunm如上所示排序。数据库显示给用户的页面每行3行。

然后，用户将从排序的第二列中搜索某些数据（通过文本输入），并希望返回的页面具有该数据的第一次出现。

例如，用户输入F，返回第2页，因为它按照排序顺序包含第一次出现的F。

例如，用户输入C，返回第1页，因为它按照排序顺序包含第一个（且仅）出现的C。

要做什么查询？

我的第一个想法是找到第一次出现的行，计算它在哪个页面，然后正常查询该页面。这是最有效的方法，还是有一些功能的构建。

谢谢。

-SQLite（通过C ++ API）

-Visual Studio 2003.NET

解决方案

我认为你的第一个想法是正确的。这是因为在数据库中有隐藏的信息并不是很常规，并且对此进行了基本查询。你的隐藏的信息这里是在哪个页面上的东西，这只是基于哪一行，如果按某个列排序。

是不同的方式来解决这个问题。我认为最常见的一个是当用户搜索F时不会显示第2页，而是显示第2.66页（也有三个项目）。它有一些缺点，但它也有优于你的方法的优点。另一种方法是在表中添加一列，该列描述哪个页面是什么。这有一个缺点，只要你坚持公式分割就是冗余数据。如果将数据添加到表的结束以外的任何其他位置，也是有问题的。它有更多的灵活性，您可以决定给某个页面额外的项目。

------------------------
|      |     A   |     |
------------------------
|      |     B   |     |
------------------------
|      |     C   |     |
------------------------ -----Page 1
|      |     D   |     |
------------------------
|      |     E   |     |
------------------------
|      |     F   |     |
------------------------ -----Page 2
|      |     F   |     |
------------------------
|      |     F   |     |
------------------------
|      |     G   |     | -----Page 3

Please excuse my ascii art, never done it before.

I have a database with the second colunm sorted as shown above. The database displays to the user in "pages" of 3 rows each.

The user is then going to seach (via a text input) for some data from the sorted second column and wants the page returned that has the first occurance of that data.

For example, the user enters "F", page 2 is returned as it contains the first occurance of F in this sort order.

For example, the user enters "C", page 1 is returned as it contains the first (and only) occurance of C in this sort order.

What is the query to do this?

My first thought is to find the row of the first occurance, calculate what page it is in and then query that page as normal. Is that the most efficient way, or is there some build in functionality.

Thank you.

-SQLite (via C++ API)
-Visual Studio 2003.NET

解决方案

I think your first thought was the correct one. This is because is it not very regular to have "hidden info" in your database, and base queries on that. Your hidden info here is on which page something is, which is merely based on which row it is if sorted by a certain column.

There are different ways to approach this, though. The most common one, I think, would be not to display page 2 when a user searches for F, but to display page 2.66 (which also has three items). It has some drawbacks, but it also has advantages over your method. Another way would be to add a column to the table which describes on which page something is. This has the disadvantage that as long as you are sticking to the "formula division" it is redundant data. It also is problematic if data is added to any other place than the "end" of the table. It does have the added flexibility that you can decide to give a certain page an extra item.

这篇关于如何查询SQL数据库以返回第一个“页面”包含指定的数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！