如何为给定的行集创建某种迭代器（或人工id）？ [英] How to create some kind of iterator (or artificial id) for a given set of rows?

问题描述

很抱歉这个愚蠢的标题，但我根本就不知道怎么称呼它。

Sorry for the dumb title, but I simply don't know how to call it.

这就是我想要的。查看以下查询：

Here's what I want. Look at the following query:

SELECT *
FROM (
  SELECT xyz
)
JOIN ...
ORDER BY RANDOM()

我想知道查询结束时 xyz 行的初始顺序。所以我想到了这样的东西（伪SQL）：

I want to know the initial order of the xyz rows when the query is over. So I thought about something like this (pseudo SQL):

iterator = 1;
SELECT *
FROM (
  SELECT iterator++, xyz
)
JOIN ...
ORDER BY RANDOM()

所以之后我的结果集将如下所示：

So afterwards my result set will look like this:

iterator | some other data
--------------------------
5        | ...
1        | ...
6        | ...
8        | ...
4        | ...
2        | ...
7        | ...
3        | ...

因此，之后可以重新创建原始订单。

So it would afterwards be possible to recreate the original order.

您现在可以说：为什么您不只是使用 id 选择 xyz ？简单：没有 id 。 xyz 数据是用户输入，我想在查询中为其创建一个人为的 id 。

May you will now say: Why you don't simply use the id where you select xyz? Simple: There's no id. The xyz data is user input and I want to create an artificial id for that within the query.

谢谢。

推荐答案

你应该可以使用 row_number（）（这是一个窗口函数）分配你想要的迭代器。这将为每一行创建一个序列号：

You should be able to use row_number() (which is a windowing function) to assign the "iterator" that you want. This will create a sequenced number for each row:

select *
from
(
  select col,
     row_number() over(order by col) rn
  from yourtable
) src
order by random()

请参阅 SQL Fiddle with演示

这篇关于如何为给定的行集创建某种迭代器（或人工id）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！