MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


5.7.39


create table Cards_in_decks (id serial, player_id int);
insert into Cards_in_decks (player_id) values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1);
insert into Cards_in_decks (player_id) select 2 from Cards_in_decks union all select 3 from Cards_in_decks

Records: 40  Duplicates: 0  Warnings: 0



Records: 80  Duplicates: 0  Warnings: 0


create table Players (game_id int, id int)
  select distinct 1 game_id, player_id id from Cards_in_decks;

Records: 3  Duplicates: 0  Warnings: 0


select player_id,count(*) from Cards_in_decks group by 1;


player_id
count(*)


1
40

2
40

3
40


select * from Players


game_id
id


1
1

1
2

1
3


create table Cards_in_game_decks like Cards_in_decks
insert into Cards_in_game_decks select * from (
  (select * from Cards_in_decks where Cards_in_decks.player_id=(select id from Players where game_id=1 order by id limit 0,1) order by rand() limit 10)
  union all
  (select * from Cards_in_decks where Cards_in_decks.player_id=(select id from Players where game_id=1 order by id limit 1,1) order by rand() limit 10)
  union all
  (select * from Cards_in_decks where Cards_in_decks.player_id=(select id from Players where game_id=1 order by id limit 2,1) order by rand() limit 10)
  union all
  (select * from Cards_in_decks where Cards_in_decks.player_id=(select id from Players where game_id=1 order by id limit 3,1) order by rand() limit 10)
  union all
  (select * from Cards_in_decks where Cards_in_decks.player_id=(select id from Players where game_id=1 order by id limit 4,1) order by rand() limit 10)
  union all
  (select * from Cards_in_decks where Cards_in_decks.player_id=(select id from Players where game_id=1 order by id limit 5,1) order by rand() limit 10)
) random_cards

Records: 30  Duplicates: 0  Warnings: 0


select * from Cards_in_game_decks;


id
player_id


9
1

14
1

24
1

29
1

30
1

32
1

34
1

35
1

38
1

39
1

43
2

44
2

53
2

56
2

58
2

59
2

68
2

70
2

75
2

79
2

81
3

88
3

89
3

94
3

100
3

102
3

111
3

114
3

116
3

119
3

id	player_id
9	1
14	1
24	1
29	1
30	1
32	1
34	1
35	1
38	1
39	1
43	2
44	2
53	2
56	2
58	2
59	2
68	2
70	2
75	2
79	2
81	3
88	3
89	3
94	3
100	3
102	3
111	3
114	3
116	3
119	3

id	player_id
9	1
14	1
24	1
29	1
30	1
32	1
34	1
35	1
38	1
39	1
43	2
44	2
53	2
56	2
58	2
59	2
68	2
70	2
75	2
79	2
81	3
88	3
89	3
94	3
100	3
102	3
111	3
114	3
116	3
119	3

id	player_id
9	1
14	1
24	1
29	1
30	1
32	1
34	1
35	1
38	1
39	1
43	2
44	2
53	2
56	2
58	2
59	2
68	2
70	2
75	2
79	2
81	3
88	3
89	3
94	3
100	3
102	3
111	3
114	3
116	3
119	3