MySQL根据2列的值选择order。

I have a table like this:

我有一张这样的桌子:

CREATE TABLE rows(
    UniqueID VARCHAR(225),
    Previous VARCHAR(225),
    Next VARCHAR(225)
);

With content, that looks like this:

内容是这样的:

+----------+-----------+-----------+
| UniqueID | Previous  | Next      |
+----------+-----------+-----------+
|    676   | undefined |       219 |
|    890   |       219 | undefined |
|    219   |       676 |       890 |
+----------+-----------+-----------+

As you can see, the rows have UID's, which the Previous and Next columns refer to.

如您所见，这些行有UID，前一列和下一列都引用UID。

What I now want, is to write a SELECT * statement, that would order all the results, by the Previous and Next fields. The undefined values mark the end elements. How could I achieve that? In the case of the table showed above, the order I'd want is what's shown there, with the last 2 row positions swapped, so Next of row X Points to a UID of row Y, that has a Previous that points to the UID of the row X. etc.

我现在想要的是，写一个SELECT *语句，它将根据前一个和下一个字段对所有结果排序。未定义的值标记结束元素。我怎么能做到呢?在上面显示的表格中，我想要的顺序是这里显示的，最后两行位置互换，所以下一行X指向Y行的UID，它前面有一个指向X行的UID，等等。

3 个解决方案

#1

What you're trying to create is a recursive query. Unfortunately, MySQL does not make this easy. There are relatively simple solutions if the parents always have an index greater than the children, but that is not the case here. There are several questions discussing this type of problem. The following question has answers that explore the different ways to attempt this type of query including using stored procedures.

您要创建的是一个递归查询。不幸的是，MySQL并不容易做到这一点。如果父母的指数总是大于孩子，有相对简单的解决办法，但这里不是这样。讨论这类问题有几个问题。下面的问题给出了一些答案，探讨了尝试这种查询的不同方式，包括使用存储过程。

How to do the Recursive SELECT query in MySQL?

如何在MySQL中执行递归选择查询?

Going with the stored procedure idea, you could try something like:

按照存储过程的想法，您可以尝试以下内容:

CREATE PROCEDURE getInOrder()
  BEGIN
    DECLARE child_id VARCHAR(256);
    DECLARE prev_id VARCHAR(256);
    SELECT UniqueID INTO prev_id FROM rows WHERE Previous = 'undefined';
    SELECT `Next` INTO child_id
    FROM rows WHERE UniqueID = prev_id;
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_table AS (SELECT * FROM rows WHERE 1=0);
    TRUNCATE TABLE temp_table;
    WHILE child_id <> 'undefined' DO
      INSERT INTO temp_table SELECT * FROM rows WHERE UniqueID = prev_id;
      SET prev_id = child_id;
      SELECT `Next` INTO child_id
      FROM rows WHERE UniqueID = prev_id;
    END WHILE;
    INSERT INTO temp_table SELECT * FROM rows WHERE UniqueID = prev_id;
    SELECT * FROM temp_table;
  END;

You can then call the stored procedure to retrieve the table in order.

然后可以调用存储过程来按顺序检索表。

Working example: http://sqlfiddle.com/#!9/085dec/2

工作示例:http://sqlfiddle.com/ ! 9/085dec / 2

#2

ORDER BY IFNULL(prev, ''),         -- some value lower than the rest
         IFNULL(next, 'zzzzz')     -- some value higher than all values

(Technically, the first part could be simply prev, without the IFNULL.)

(技术上，第一部分可以是简单的prev，没有IFNULL。)

If the ids are really numbers, you should use a numeric datatype such as INT UNSIGNED. If they are really strings, do you need 225?

如果id真的是数字，您应该使用数字数据类型，比如INT UNSIGNED。如果它们是字符串，你需要225吗?

This assumes that prev < next -- Is that necessarily the case? It seems like arbitrary links might not maintain that. If you need to look at next to load the next row based on UniqueId, the code is much more complex.

这里假设prev < next——这是必然的吗?似乎任意的链接可能无法维护这一点。如果您需要查看next以基于UniqueId加载下一行，那么代码将更加复杂。

#3

I think this request lacks on details.

我认为这个要求缺乏细节。

But, you want the final result to be like this?

但是，你希望最终结果是这样的吗?

+----------+-----------+-----------+
| UniqueID | Previous  | Next      |
+----------+-----------+-----------+
|    676   | undefined |       219 |
|    219   |       676 |       890 |
|    890   |       219 | undefined |
+----------+-----------+-----------+

If I'm right, you can achieve it with (I named the table as demo):

如果我是对的，你可以通过(我将表格命名为demo):

SELECT d.* FROM (
    SELECT UniqueID, IF(Previous IS NULL, -1, Previous) AS Previous, IF(Next   IS NULL, 999999999999, Next) as Next
    FROM demo
)t
JOIN demo d ON d.UniqueID = t.UniqueID
ORDER BY t.Next, t.Previous
;

So, when Previous is NULL you put it with -1 to ensure he's is the first on the list and when Next is NULL you put it with a very high value to ensure it will be the last on the list... then you just have to order the query by Previous and Next.

所以，当Previous值为空时，你将它与-1放在一起，以确保它是列表中的第一个，当Next为空时，你将它放在一个非常高的值，以确保它是列表中的最后一个。然后，您只需按“前”和“下”对查询进行排序。

I must stress that this solution is focused on presented data.

我必须强调，这个解决方案关注的是所提供的数据。

#1