MySQL 订购帮助！

发布于 2024-10-01 03:52:43 字数 278 浏览 2 评论 0原文

我希望按特定顺序进行订购。我知道我可以修改整个数据库，但随后我需要修改整个代码库。

我所拥有的是表“游戏”中的一列，称为“状态”。

所以...

  SELECT * 
    FROM games 
ORDER BY status ASC  -- Will retrieve results going from 0 then 1 then 2

我正在寻找的是能够按 1 然后 0 然后 2 对其进行排序。

有什么想法吗？？？

原文

I am looking to do an order by in a certain order. I know I can modify the entire database but I would then need to modify the entire code base.

What I am have, is a column in a table 'games' called 'status'.

So...

  SELECT * 
    FROM games 
ORDER BY status ASC  -- Will retrieve results going from 0 then 1 then 2

What I am looking for is to be able to order it by 1 then 0 then 2.

Any ideas???

分享到QQ

分享到微博

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

发布评论

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

情独悲 2024-10-08 03:52:43

如果我理解正确，使用 CASE 表达式：

  SELECT g.* 
    FROM GAMES g
ORDER BY CASE g.status 
           WHEN 0 THEN 1
           WHEN 1 THEN 2
           WHEN 2 THEN 3
         END

使用FIND_IN_SET 函数：

  SELECT g.* 
    FROM GAMES g
ORDER BY FIND_IN_SET(g.status, '0,1,2')

使用 < a href="http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field" rel="nofollow">字段函数：

  SELECT g.* 
    FROM GAMES g
ORDER BY FIELD(g.status, 0, 1, 2)

If I understand correctly, using a CASE expression:

  SELECT g.* 
    FROM GAMES g
ORDER BY CASE g.status 
           WHEN 0 THEN 1
           WHEN 1 THEN 2
           WHEN 2 THEN 3
         END

Using FIND_IN_SET function:

  SELECT g.* 
    FROM GAMES g
ORDER BY FIND_IN_SET(g.status, '0,1,2')

Using FIELD function:

  SELECT g.* 
    FROM GAMES g
ORDER BY FIELD(g.status, 0, 1, 2)

回复收藏 0 原文

迷荒 2024-10-08 03:52:43

您可以使用 FIELD() ：

SELECT *
FROM games
ORDER BY FIELD(status, 1, 0, 2)

但最好多解释一下您想要做什么，即您的表包含什么。

You can use FIELD() :

SELECT *
FROM games
ORDER BY FIELD(status, 1, 0, 2)

But it might be better to explain a bit more what you want to do, i.e. what your table contains.

回复收藏 0 原文

半城柳色半声笛 2024-10-08 03:52:43

我打算发布与 OMG Ponies 相同的方法，所以+1。这是我学习这项技术的有用网站：

http://www.shawnolson .net/a/722/mysql-任意-ordering.html

回复收藏 0 原文

何时共饮酒 2024-10-08 03:52:43

另一种选择是为状态列创建一个验证表，称为 VAL_GAME_STATUS 之类的名称。 GAMES.STATUS 将是 VAL_GAME_STATUS 的外键，VAL_GAME_STATUS 看起来像这样

CREATE TABLE VAL_GAME_STATUS  -- Oracle format - modify as needed for other DB
  (STATUS      NUMBER PRIMARY KEY,
   DESCRIPTION VARCHAR2(50) NOT NULL UNIQUE,
   SORT_ORDER  NUMBER NOT NULL UNIQUE);

，从 GAMES 获取排序结果的查询就变成了。

SELECT g.*
  FROM GAMES g
INNER JOIN VAL_GAME_STATUS s
  USING (STATUS)
ORDER BY s.SORT_ORDER;

这里的缺点是您需要进行联接来对数据进行排序，但这是一个次要的打击因为 VAL_GAME_STATUS 是一个小表，可能可以保存在内存中。优点是您可以即时更改排序顺序，而无需更改代码。添加具有适当排序顺序的新状态值也很容易完成。

分享并享受。

Another option would be to have a validation table for the status column, called something like VAL_GAME_STATUS. GAMES.STATUS would be a foreign key to VAL_GAME_STATUS, and VAL_GAME_STATUS migt look like

CREATE TABLE VAL_GAME_STATUS  -- Oracle format - modify as needed for other DB
  (STATUS      NUMBER PRIMARY KEY,
   DESCRIPTION VARCHAR2(50) NOT NULL UNIQUE,
   SORT_ORDER  NUMBER NOT NULL UNIQUE);

Given this, the query to fetch sorted results from GAMES becomes

SELECT g.*
  FROM GAMES g
INNER JOIN VAL_GAME_STATUS s
  USING (STATUS)
ORDER BY s.SORT_ORDER;

The disadvantage here is that you need to do a join to get the data sorted, but it's a minor hit as VAL_GAME_STATUS is a small table which can probably be held in memory. The advantage is that you can change the sort order on the fly without touching your code. Adding new status values with an appropriate sort order is also easily done.

Share and enjoy.

回复收藏 0 原文

~没有更多了~