I have an uid and anchorUid relationship table named userAnchor like picture above, now I need to select some data from this table;

1. select all line where anchorUid=1, in this example, is uid 1 to 10;
2. random select 1 lines from top 15% of result in step 1, in this example, the result of uid is 1;
3. random select 2 lines from middle 35% of result in step 1, in this example, the result of uid will be random in 2-5;
4. random select 3 lines from bottom 50% of result in step 1, in this example, the result of uid will be random in 6-10;

My question is can I execute the query in one sql?

CodePudding user response：

WITH
cte1 AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY id) rn
          FROM test
          WHERE anchor_id = 1 ),
cte2 AS ( SELECT MAX(rn) cnt
          FROM cte1 ),
cte3 AS ( SELECT id,
                 ROUND(cnt * percent_from / 100)   1 range_from, 
                 ROUND(cnt * percent_till / 100) range_till
          FROM cte2 
          CROSS JOIN percents ),
cte4 AS ( SELECT id, ROUND(range_from   RAND() * (range_till - range_from)) random_id
          FROM cte3)
SELECT cte1.id, cte1.uid, cte1.anchor_id
FROM cte4
JOIN cte1 ON cte4.random_id = cte1.rn;

DEMO fiddle with explanations for each step.

Solution is not compacted/optimized. You may do this yourself.