如何从子表中选择一个随机标识？

Question

我使用的是10.4.13-MariaDB。我有一个父表和一个子表。

父表(任务)

id parent_id
4    # <- Main (parent theme) Level 2
5    4 <- child `discovered check` (level 2)
6    4 <- child  `windmill`  (level 2)
7    # <- Main (parent theme) Level 3
8    7 <- child `discovered check - level 3` (level 3)
9    7 <- child  `windmill - level 3`  (level 3)
10    # <- Main (parent theme) Level 1
11    10 <- child `discovered check - winning a piece` (level 1)
12    10 <- child  `discovered check - mate in one`  (level 1)

在界面上，它看起来就像

​

​

我有一个儿童桌子(拼图)-拼图只能属于儿童主题。

 id      task_id
 52         5 <- belongs to `discovered check` (level 2)
 61         6 <- belongs to `windmill` (level 2)
 25         6 <- belongs to `windmill` (level 2)
 70         11 <- belongs to `discovered check - winning a piece` (level 1)
 53         12 <- belongs to `discovered check - mate in 1` (level 1)
 62         9 <- belongs to `windmill - level 3` (level 3)
 27         9 <- belongs to `windmill - level 3` - (level 3)
 72         8 <- belongs to `discovered check - level 3` - (level 3)

我想从每个层次得到一个随机的谜题。如何编写适当的查询？

预期结果：

task_id  id
  5      52 <- random puzzle from level 2
  11     70 <- random puzzle from level 1
  8      72 <- random puzzle from level 3

我的小提琴：10.4&fiddle=7bed2a19a0f98abccbe06ba9e0ae358b

Answer 1

使用ROW_NUMBER()窗口函数：

with cte as (
  select p.*, row_number() over (partition by t.parent_id order by rand()) rn
  from puzzles p inner join tasks t  
  on t.id = p.task_id
)
select task_id, id
from cte 
where rn = 1 

见演示。

row_number()的分区应该由tasks的列parent_id进行，因此每个主题都有1行的结果。

Answer 2

WITH cte AS ( SELECT tasks.id task_id, 
                     puzzles.puzzle_id, 
                     row_number() over (partition by tasks.id order by rand()) rn
              FROM tasks
              JOIN puzzles ON tasks.id = puzzles.task_id )
SELECT task_id, puzzle_id
FROM cte
WHERE rn = 1;

需要版本8.0+或MariaDB 10.2+

select tasks.id task_id, rand_puzzle.puzzle_id
from tasks,
     lateral ( select puzzle_id
               from puzzles
               where tasks.id = puzzles.task_id 
               order by rand() limit 1 ) rand_puzzle;

需要版本8.0.14+。

小提琴

如果需要某些子树的数据，那么以前只从递归CTE中的tasks表中提取此子树。

Answer 3

你希望每个主要任务都有一个谜题。因此，首先分层查找属于一个主任务的所有任务(使用递归查询)，然后加入谜题，然后在每个主要任务中选择一个。

(主要任务是请求中所称的“级别”。)

with recursive cte (main_task_id, task_id) as
(
  select parent_id, task_id
  from tasks
  where parent_id in (select id from tasks where parent_id is null)
  union all
  select cte.main_task_id, t.task_id
  from cte
  join tasks t on t.parent_id = cte.task_id
)
, randomly_numbered as
(
  select
    cte.main_task_id,
    p.task_id,
    p.id as puzzle_id,
    row_number() over (partition by main_task_id order by rand()) as rn
  from cte
  join puzzle p on p.task_id = cte.task_id
)
select
  -- main_task_id,
  task_id,
  puzzle_id
from randomly_numbered
where rn = 1
order by main_task_id;