为 SQL 连接选择单个（随机）行

发布于 2024-08-06 14:07:02 字数 638 浏览 23 评论 0原文

我有一个从多个表中选择数据的 sql 查询，但我只想匹配另一个表中的单个（随机选择）行。

我想更容易显示一些代码；）

表 K 是 (k_id, selected) 表C是(c_id, image) 表S是（c_id，日期）表 M 为 (c_id, k_id, Score)

所有 ID 列都是主键，具有适当的 FK 约束。

用英语来说，我想要的是 K 中选择 = 1 的每一行从 C 中获取随机行，其中 M 中存在带有 (K_id, C_id) 的行，其中分数高于给定值，并且其中 c.image 不为空，并且 s 中有一行带有 c_id

类似：

select k.k_id, c.c_id, m.score
 from k,c,m,s
where k.selected = 1
  and m.score > some_value
  and m.k_id = k.k_id
  and m.c_id = c.c_id
  and c.image is not null
  and s.c_id = c.c_id;

唯一的问题是这会返回 C 中符合条件的所有行 - 我只想要一个...

我可以看到如何使用PL/SQL 将所有相关行选择到集合中，然后选择随机行，但我不知道如何选择随机行。

原文

I've got an sql query that selects data from several tables, but I only want to match a single(randomly selected) row from another table.

Easier to show some code, I guess ;)

Table K is (k_id, selected)
Table C is (c_id, image)
Table S is (c_id, date)
Table M is (c_id, k_id, score)

All ID-columns are primary keys, with appropriate FK constraints.

What I want, in english, is for eack row in K that has selected = 1 to get a random row from C where there exists a row in M with (K_id, C_id), where the score is higher than a given value, and where c.image is not null and there is a row in s with c_id

Something like:

select k.k_id, c.c_id, m.score
 from k,c,m,s
where k.selected = 1
  and m.score > some_value
  and m.k_id = k.k_id
  and m.c_id = c.c_id
  and c.image is not null
  and s.c_id = c.c_id;

The only problem is this returns all the rows in C that match the criteria - I only want one...

I can see how to do it using PL/SQL to select all relevent rows into a collection and then select a random one, but I'm stuck as to how to select a random one.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

暮色兮凉城 2024-08-13 14:07:02

您可以在查询中使用“order by dbms_random.random”指令。

即：

SELECT column FROM
  (
    SELECT column FROM table
    ORDER BY dbms_random.value
  )
WHERE rownum = 1

参考文献：
http://awads.net/wp/2005/08 /09/无序排序/
http://www.petefreitag.com/item/466.cfm

you can use the 'order by dbms_random.random' instruction with your query.

i.e.:

SELECT column FROM
  (
    SELECT column FROM table
    ORDER BY dbms_random.value
  )
WHERE rownum = 1

References:
http://awads.net/wp/2005/08/09/order-by-no-order/
http://www.petefreitag.com/item/466.cfm

回复收藏 0 原文

故乡的云 2024-08-13 14:07:02

with Analytics：

SELECT k_id, c_id, score
  FROM (SELECT k.k_id, c.c_id, m.score, 
               row_number() over(PARTITION BY k.k_id ORDER BY NULL) rk
           FROM k, c, m, s
          WHERE k.selected = 1
            AND m.score > some_value
            AND m.k_id = k.k_id
            AND m.c_id = c.c_id
            AND c.image IS NOT NULL
            AND s.c_id = c.c_id)
 WHERE rk = 1

这将选择满足每个 k_id 标准的一行。如果您多次运行查询，这可能会选择同一组行。如果您想要更多随机性（每次运行生成不同的行集），您可以将 ORDER BY NULL 替换为 ORDER BY dbms_random.value

with analytics:

SELECT k_id, c_id, score
  FROM (SELECT k.k_id, c.c_id, m.score, 
               row_number() over(PARTITION BY k.k_id ORDER BY NULL) rk
           FROM k, c, m, s
          WHERE k.selected = 1
            AND m.score > some_value
            AND m.k_id = k.k_id
            AND m.c_id = c.c_id
            AND c.image IS NOT NULL
            AND s.c_id = c.c_id)
 WHERE rk = 1

This will select one row that satisfies your criteria per k_id. This will likely select the same set of rows if you run the query several times. If you want more randomness (each run produces a different set of rows), you would replace ORDER BY NULL by ORDER BY dbms_random.value

回复收藏 0 原文