从Oracle中的多行中的一列中选择一个随机数据 [英] Selecting one random data from a column from multiple rows in oracle

问题描述

我正在创建一个视图，该视图仅需要为每个客户选择一个随机行.像这样:

I am creating a view that needs to select only one random row for each customer. Something like:

select c.name, p.number
from customers c, phone_numbers p
where p.customer_id = c.id

如果此查询返回:

NAME      NUMBER
--------- ------
Customer1 1
Customer1 2
Customer1 3
Customer2 4
Customer2 5
Customer3 6

我需要这样的东西:

NAME      NUMBER
--------- ------
Customer1 1
Customer2 4
Customer3 6

Rownum无法正常工作，因为它将仅从所有6条记录中选择第一个，而我需要每个客户的第一个.我需要不会对性能造成太大影响的解决方案，因为选择数据的查询非常复杂，这仅是说明我需要的示例.预先感谢.

Rownum wont work because it will select only the first from all 6 records, and i need the first from each customer. I need solution that won't affect performance much, because the query that selects the data is pretty complex, this is just an example to explain what I need. Thanks in advance.

推荐答案

使用ROW_NUMBER()分析功能:

SELECT name,
       number
FROM   (
  SELECT c.name,
         p.number,
         ROW_NUMBER() OVER ( PARTITION BY c.id ORDER BY DBMS_RANDOM.VALUE ) AS rn
  FROM   customers c
         INNER JOIN phone_numbers p
         ON ( p.customer_id = c.id )
)
WHERE  rn = 1

这篇关于从Oracle中的多行中的一列中选择一个随机数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！