如何在 SQLite ORDER BY 上混合 ASC 和 RANDOM() [英] How to mix ASC and RANDOM() on SQLite ORDER BY

问题描述

因此，我在尝试对 2 列使用 ORDER BY 时遇到错误:

So, I'm facing an error while trying to use ORDER BY for 2 columns:

...ORDER BY val, random();

... ORDER BY val, random();

错误是:第二个 ORDER BY 术语与结果集中的任何列都不匹配"

Error is: "2nd ORDER BY term does not match any column in the result set"

我尝试了不同的方法来组合第一列的 ASC 排序和第二列的随机排序，但没有运气.

I tried different ways to combine ASC sorting for 1st column and random sorting for second column, no luck.

更新以提供更多信息

创建表 tabela (id 整数，val 文本，主键(id));

CREATE TABLE tabela ( id INTEGER, val TEXT, PRIMARY KEY(id) );

INSERT INTO tabela (val) VALUES ('paid');
INSERT INTO tabela (val) VALUES ('paid');
INSERT INTO tabela (val) VALUES ('paid');
INSERT INTO tabela (val) VALUES ('standard');
INSERT INTO tabela (val) VALUES ('standard');
INSERT INTO tabela (val) VALUES ('standard');
INSERT INTO tabela (val) VALUES ('standard');
INSERT INTO tabela (val) VALUES ('standard');

预期样本结果:

val        id
---        ---
paid       3
paid       1
paid       2
standard   5
standard   8
standard   4
standard   6
standard   7

其中 'id' 1,2,3 将在 'paid' 'val' 内随机排序，而 'id' 4 ... 8 将在 'standard' 'val' 内随机排序

where 'id' 1,2,3 will be randomly sorted within 'paid' 'val' and 'id' 4 ... 8 will be randomly sorted within 'standard' 'val'

推荐答案

select val, id from (select random() as r, * from tabela) order by val, r;

诀窍是制作一个动态表(在 (...) 内)，其中有一列带有随机值.可以通过那个订购.
请注意，此查询正在对该动态表的完整行进行排序/排序.

The trick is to make an on-the-fly table (inside the (...)) which has a column with random values. It is possible to order by that.
Note that this query is sorting/ordering complete rows of that on-the-fly table.

输出 1:

paid|1  
paid|3  
paid|2  
standard|8  
standard|5  
standard|7  
standard|6  
standard|4  

输出 2:

paid|3  
paid|1  
paid|2  
standard|5  
standard|8  
standard|7  
standard|4  
standard|6  

请原谅我的印象，您会想到对列进行排序.如果您只使用 (...) 中的子查询，它可能会帮助您看到差异，研究输出然后想象手动"对行进行排序，同时不允许更改任何的行.

Please excuse my impression that you think of ordering columns. It might help you to see the difference, if you use only the subquery in the (...), study the output and then imagine "manually" sorting the rows, while not being allowed to change any of the rows.

这是生成动态表的查询(附加排序):

This is the query making the on-the-fly table (with additional ordering):

select random() as r, * from tabela order by val, id;  

及其输出:

6112298175921944810|1|paid  
-750320757383183987|2|paid  
-4687754812847362857|3|paid  
574487853771424670|4|standard  
6662074554381494613|5|standard  
5947282373212186891|6|standard  
-695595223160523440|7|standard  
-6914056362765123037|8|standard

这篇关于如何在 SQLite ORDER BY 上混合 ASC 和 RANDOM()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！