blocks|key|1127708|text|您可以在如下所示的新查询中使用您的查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1127709|SELECT+++++++
+++movieName+
FROM+++++++
+++movies
WHERE
+++movieName+not+in+(SELECT+film_id+FROM+film+ORDER+BY+random()+LIMIT+20)
ORDER+BY+++++++
+++random()+
LIMIT+10;|code-block|syntax|javascript|1127710|您不会看到第一次查询时选择的20部电影。在这种情况下，只有选择20部电影的结果，然后选择剩余30部电影中的10部。|1127711|请记住，如果您首先使用语句，然后执行此查询，则subselect(您的查询)将返回比第一次执行时更多的随机电影。|1127712|希望你能帮助我！|1127713|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

You could use your query in an new query like that:

<pre><code>SELECT 
 movieName 
FROM 
 movies
WHERE
 movieName not in (SELECT film_id FROM film ORDER BY random() LIMIT 20)
ORDER BY 
 random() 
LIMIT 10;
</code></pre>

You won't see the 20 movies selected with your first query. In this case theres just the result of selecting 20 movies and than selecting 10 of the remaining 30.

Keep in mind, that if you are are using your statement first, and this query afterwards, the subselect(your query) will return orther random movies than by the first time it is executed.

Hope that hepls!

blocks|key|1255294|text|您需要存储已在某个位置显示的电影的名称。然后，当您重新运行该语句时，向查询中添加一个where+moviename+not+in+(....)，以排除已经显示的那些内容。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1255295|如果您可以在请求之间保持与数据库的连接打开，则可以使用临时表来存储已经显示的电影：|1255296|create+temporary+table+displayed_movies(moviename+text);|code-block|syntax|javascript|1255297|然后，您可以使用以下语句显示电影：|1255298|with+selected+as+(
++select+moviename
++from+movies+
++where+moviename+not+in+(select+d.moviename+
++++++++++++++++++++++++++from+displayed_movies+d)
++order+by+random()+
++limit+20
),+inserted+as+(
+++insert+into+displayed_movies+(moviename)
+++select+moviename
+++from+selected+++++++
)+
select+*
from+selected;|1255299|每次运行上述语句时，它都会选择一组不同的(随机)电影。如果显示了所有电影，则不会返回任何内容。|1255300|再说一次:只有在每次调用之间保持数据库连接打开的情况下，这才能起作用。|1255301|如果不能保持连接打开(这意味着不能使用临时表)，则需要记住应用程序中的这些名称并生成适当的where+moviename+not+in+(...)条件。|1255302|entityMap^0|16|T|0|0|0|0|0|0|0|19|S|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|11|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|12|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|13|8|@]|D|@]|E|$]]|$1|O|3|P|5|J|7|14|8|@]|D|@]|E|$K|L]]|$1|Q|3|R|5|6|7|15|8|@]|D|@]|E|$]]|$1|S|3|T|5|6|7|16|8|@]|D|@]|E|$]]|$1|U|3|V|5|6|7|17|8|@$9|18|A|19|B|C]]|D|@]|E|$]]|$1|W|3|-4|5|6|7|1A|8|@]|D|@]|E|$]]]|X|$]]

You need to store the names of the movies that were already displayed somewhere. Then when you re-run the statement add a <code>where moviename not in (....)</code> to the query to exclude those that you already displayed.

If you can keep the connection to the database open between the requests, you can use a temporary table to store the already displayed movies:

<pre><code>create temporary table displayed_movies(moviename text);
</code></pre>

You can then use the following statement to display the movies:

<pre><code>with selected as (
 select moviename
 from movies 
 where moviename not in (select d.moviename 
 from displayed_movies d)
 order by random() 
 limit 20
), inserted as (
 insert into displayed_movies (moviename)
 select moviename
 from selected 
) 
select *
from selected;
</code></pre>

Each time you run the above statement, it will select a different (random) set of movies. If all movies are displayed nothing will be returned. 

Again: this will only work if you keep the connection to the database open between each call.

If you can't keep the connection open (which means you can't use the temporary table) you need to remember those names in your application and generate the appropriate <code>where moviename not in (...)</code> condition.

blocks|key|1127722|text|生成随机密钥key，然后按md5(movieName+%7C%7C+key)排序。跟踪这个键，这样您就可以使用具有相同键的OFFSET请求下一组元组。使用相同的键两次将导致相同的顺序。使用不同的键将导致不同的顺序。这看起来是随机的，因为像md5这样的哈希函数对于相似的输入返回完全不同的结果。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1127723|entityMap^0|6|3|D|L|1L|6|37|3|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]|$9|M|A|N|B|C]|$9|O|A|P|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|G|$]]

Generate a random key <code>key</code>, then order by <code>md5(movieName || key)</code>. Keep track of this key so you can request the next set of tuples using <code>OFFSET</code> with the same key. Using the same key twice will result in the same order. Using a different key will result in a different order. This appears random because hash functions like <code>md5</code> return completely different results for similar inputs.

If I select 20 random movies from 50 movies total,

<pre><code>SELECT movieName
FROM movies 
ORDER BY random() 
LIMIT 20
</code></pre>

Then how do I continue select 10 random from the remaining 30 movies?

If I keep using code above, there is a duplicate key value error.

How to get the remaining data from a select that returns random rows

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

如果我从总共50部电影中随机选择20部电影，SELECT movieNameFROM movies ORDER BY random() LIMIT 20那么我如何继续从剩下的30部电影中随机选择10部呢？如果我继续使用上面的代码，就会出现重复键值错误。

问如何从返回随机行的select中获取剩余数据
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何从返回随机行的select中获取剩余数据EN