------------------------
| | A | |
------------------------
| | B | |
------------------------
| | C | |
------------------------ -----Page 1
| | D | |
------------------------
| | E | |
------------------------
| | F | |
------------------------ -----Page 2
| | F | |
------------------------
| | F | |
------------------------
| | G | | -----Page 3
Please excuse my ascii art, never done it before.
请原谅我的ascii艺术,从来没有做过。
I have a database with the second colunm sorted as shown above. The database displays to the user in "pages" of 3 rows each.
我有一个数据库,第二个colunm排序如上所示。数据库以“每页3行”的“页面”显示给用户。
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.
例如,用户输入“F”,返回第2页,因为它包含此排序顺序中F的第一次出现。
For example, the user enters "C", page 1 is returned as it contains the first (and only) occurance of C in this sort order.
例如,用户输入“C”,返回第1页,因为它包含此排序顺序中C的第一个(也是唯一的)出现。
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
-SQLite(通过C ++ API)-Visual Studio 2003.NET
2 个解决方案
#1
2
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.
但是,有不同的方法来解决这个问题。我认为,最常见的一种是当用户搜索F时不显示第2页,而是显示第2.66页(其中还有三个项目)。它有一些缺点,但它也比你的方法有优势。另一种方法是在表格中添加一列,用于描述哪些页面是什么。这样做的缺点是,只要你坚持“公式划分”,它就是冗余数据。如果将数据添加到除表的“结尾”之外的任何其他位置,也会出现问题。它确实具有额外的灵活性,您可以决定为某个页面添加额外的项目。
#2
1
I have a database with the second colunm sorted as shown above.
我有一个数据库,第二个colunm排序如上所示。
No, you don't. SQL tables are unordered. What you have is a SELECT result that just happens to be in ROWID order as an implementation detail. If you want to base queries on the row number, then it should be an explicit column:
不,你没有。 SQL表是无序的。你所拥有的是SELECT结果,恰好是ROWID顺序作为实现细节。如果要基于行号查询,则它应该是显式列:
CREATE TABLE T(
ID INTEGER PRIMARY KEY AUTOINCREMENT,
Letter CHAR
);
#1
2
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.
但是,有不同的方法来解决这个问题。我认为,最常见的一种是当用户搜索F时不显示第2页,而是显示第2.66页(其中还有三个项目)。它有一些缺点,但它也比你的方法有优势。另一种方法是在表格中添加一列,用于描述哪些页面是什么。这样做的缺点是,只要你坚持“公式划分”,它就是冗余数据。如果将数据添加到除表的“结尾”之外的任何其他位置,也会出现问题。它确实具有额外的灵活性,您可以决定为某个页面添加额外的项目。
#2
1
I have a database with the second colunm sorted as shown above.
我有一个数据库,第二个colunm排序如上所示。
No, you don't. SQL tables are unordered. What you have is a SELECT result that just happens to be in ROWID order as an implementation detail. If you want to base queries on the row number, then it should be an explicit column:
不,你没有。 SQL表是无序的。你所拥有的是SELECT结果,恰好是ROWID顺序作为实现细节。如果要基于行号查询,则它应该是显式列:
CREATE TABLE T(
ID INTEGER PRIMARY KEY AUTOINCREMENT,
Letter CHAR
);