MySQL Order By 替代值 [英] MySQL Order By alternate values

问题描述

我在 MySQL 查询 Order By 时遇到问题.

Im having trouble in MySQL query Order By.

首先，我有一个包含 10000 多条记录的表(tbl_records).该表有一个名为 rec_id 的主键和一个外键 rec_f_id.

First I have a table(tbl_records) that contains 10000+ records. This table has a primary key called rec_id and a foreign key rec_f_id.

rec_f_id 只有两种值(555、666).

rec_f_id has only two kinds of value (555, 666).

现在我的问题是如何收集表中以 rec_f_id 替代值排序的记录.

Now my problem is how can I gather the records on the table that orders in alternate value of rec_f_id.

例如

下面是一个虚拟的tbl记录

Below is a dummy tbl records

 | rec_id   |   rec_f_id |
 |2         |666         |
 |3         |555         |
 |7         |555         |
 |8         |666         |
 |9         |555         |
 |12        |666         |
 |25        |555         |
 |31        |555         |
 |84        |666         |
 |89        |555         |
 |91        |555         |
 |92        |666         |
 |113       |666         |
 |118       |666         |
 |125       |555         |
 |132       |555         |
 |170       |555         |
 |184       |666         |


 SELECT * FROM tbl_records ORDER BY FIELD(rec_f_id, 555, 666) LIMIT 100;

上述查询仅返回 rec_f_id = 555 的记录.我想要的是

The above query only returns record with rec_f_id = 555. What I want to have is

 | rec_id   |   rec_f_id |
 |31        |555         |
 |12        |666         |
 |3         |555         |
 |8         |666         |
 |25        |555         |
 |2         |666         |
 |7         |555         |
 |84        |666         |
 |9         |555         |

 ...

谢谢！

推荐答案

这是使用用户定义变量的一个选项.基本上它为每个组创建一个 Row Number，然后通过它与 rec_f_id 字段一起排序:

Here's one option using user-defined variables. Basically it creates a Row Number per Group, and then orders by it along with the rec_f_id field:

SELECT rec_id, rec_f_id
FROM (
  SELECT rec_id, rec_f_id,
    @rn:=IF(@prev=rec_f_id,@rn+1,1) rn,
    @prev:=rec_f_id
  FROM tbl_records
    JOIN (SELECT @rn:=0, @prev:=0) t
  ORDER BY rec_f_id
  ) t
ORDER BY rn, rec_f_id

• 示例 SQL 小提琴

这篇关于MySQL Order By 替代值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！