问题描述

我有一个需要按特定顺序显示的项目表，但该顺序可以更改.可以在开头、结尾或中间添加项目，并且可以重新排列项目.我如何设置表格以跟踪该订单，以便易于修改，但也可以通过单个查询按顺序获取列表?

I have a table of items that need to be displayed in a certain order, but that order can be changed. Items can be added at the beginning, end, or in the middle, and items can be rearranged. How can I set up the table to keep track of that order in such a way that it's easy to modify but the list can also be fetched in order with a single query?

例如，我可以有一个NEXT_ID"列来执行链接列表样式，但是我将如何运行 SELECT 查询以按 NEXT_ID 链的顺序获取行?

For example, I could have a "NEXT_ID" column to do it linked list-style, but then how would I run a SELECT query to get the rows in order of the NEXT_ID chain?

为我可能遗漏的超级明显的解决方案提前道歉.

Apologies in advance for the super-obvious solution I'm probably missing.

推荐答案

我经常遇到这个问题，我用一个简单的解决方案解决了它:一个名为 Sort Order(或 DisplayOrder，无论你的船真正漂浮的东西)的额外列.这让我可以灵活地使用自动生成、自动递增的 ID 列并具有特殊的预定义排序.

I have this problem often, and I solved it with a simple solution : an extra column called Sort Order (or DisplayOrder, whatever floats your boat really) . This allows me the flexibility to use auto-generated, auto-incremented ID column and have a special pre-defined sort.

在我的例子中，我需要它们按字母顺序从数据库中出来，除了像其他"和不适用"这样的项目总是在最后.

In my case, I need them to come out of the database with an alphabetical order except that some items like "Other" and "N/A" are always last.

ProdID ProdText SortOrder
2      "Anchovies"    1
3      "Rivet"        2
4      "N/A"          4
5      "Other"        3

SELECT ProdID, ProdText ORDER BY Sort Order

这篇关于在 MySQL 中创建链表或类似队列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！