Use Oracle analysis functions to randomly extract N records
Simulate a song table with a song ID and Author ID, and initialize some data
Create table music (
Music_id int primary key,
Userid int
);
Insert into music (music_id)
Select rownum rn from dual connect by level <1000;
Update music set userid = mod (music_id, 5 );
Commit;
Requirements:
Each author randomly selects three of his works.
The SQL statement is as follows:
Select * from
(
Select music_id, userid, ROW_NUMBER () OVER (partition by userid order by dbms_random.random () rn from music
)
Where rn <= 3;
First execution:
Second execution:
This method is only effective for Oracle (using its analysis functions)
Detailed examples of Oracle function GREATEST Functions
Single-row conversion functions of Oracle Functions
Oracle functions that generate dynamic prefixes and auto-increment numbers
Replace () for Oracle Functions ()
Oracle Functions
Differences in case and decode usage and Performance Comparison of Oracle Functions