Mysql:查询默认情况下的订单和排名(pin/hold-up条目?)

Question

我有一个产品表，其中包含我的所有产品。那些产品表永远都是新产品。然而，我希望有可能"；"托起"/"pin"；将某些产品添加到返回的查询集合中的某个位置。

也就是说，我想设置类似rank_index的内容，其中包含产品在返回的查询集合中应该具有的编号。

示例：

id     title                rank_index
1      An awesome product
2      Another product      5
3      Baby car
4      Green carpet         2
5      Toy        

假设默认顺序为id。但是，因为rank_index是为具有id的产品设置的，所以我希望获得具有以下返回ID顺序的集合：1, 4, 3, 5, 2。

这有可能吗？rank_index专栏只是我的一个想法。我的意思是。。我也可以在php侧执行此操作，并执行仅包括不带rank_index的products和仅包含带index_rank的products的正常查询，然后在php侧手动订购它们。

然而，由于这需要大量的时间和处理能力，我正在寻找一个由数据库完成的解决方案。。。有什么想法吗？

顺便说一句：如果这有什么不同的话，我会用Laravel 8。

问候

Answer 1

这是一个非常棘手的问题。如果您尝试另一种设置连续值的方法，比如2和3，您会发现它们不起作用。

可能有更简单的方法来解决这个问题。但是，这里有一种暴力的方法。

1. 它通过枚举原始表中的行来构造派生表
2. 它(使用left join(将所有力排序值添加到此表中
3. 它通过枚举table1和派生表中的空槽来加入其余值

所以：

with recursive n as (
select row_number() over (order by id) as n
from table1 t1
),
nid as (
select n.n, t1.id
from n left join
table1 t1
on t1.rank_index = n.n
),
nids as (
select n.n, coalesce(n.id, t1.id) as id
from (select nid.*, sum(nid.id is null) over (order by nid.n) as seqnum
from nid 
) n left join
(select t1.*, row_number() over (order by id) as seqnum
from table1 t1
where rank_index is null
) t1
on n.seqnum = t1.seqnum
)
select t1.*
from nids join
table1 t1
on t1.id = nids.id
order by nids.n;

Answer 2

如果rank_index不为空，则使用它作为排序，否则使用id：

由于您希望rank_index领先于id，因此需要进行-0.5调整：

SELECT *
FROM table
ORDER BY IF(rank_index IS NULL, id, rank_index - 0.5)

Answer 3

您可以使用IF子句，并使用正确的数字来获得正确的顺序，因此

CREATE TABLE table1 (
`id` INTEGER,
`title` VARCHAR(18),
`rank_index` INT
);
INSERT INTO table1
(`id`, `title`, `rank_index`)
VALUES
('1', 'An awesome product', NULL),
('2', 'Another product', '5'),
('3', 'Baby car', NULL),
('4', 'Green carpet', '2'),
('5', 'Toy', NULL);
SELECT *
FROM table1
ORDER BY IF(rank_index IS NULL, id, rank_index + .01)

+----+--------------------+------------+
| id | title              | rank_index |
+----+--------------------+------------+
| 1  | An awesome product | NULL       |
| 4  | Green carpet       | 2          |
| 3  | Baby car           | NULL       |
| 5  | Toy                | NULL       |
| 2  | Another product    | 5          |
+----+--------------------+------------+

db<gt；小提琴这里