0

I have a MySQL table where a number between 0 and 99 is inserted every now an then. Every time a new value is inserted I need to count how many numbers between 0 and 99 does not appears in the last 100 previous records and update not_in_last_100_repetitions.

You can assume the structure of my table is:

num (int, primary, auto-increment) | winner (varchar) | not_in_last_100_repetitions (int)
  • You can assume the structure of my table is Where is the field the next (random?) "number between 0 and 99" is inserted into? in the last 100 previous records While ordering by num, is it? – Akina May 15 '18 at 13:53
  • @Akina Yes, ordering by num – Cool Guy May 15 '18 at 17:52
0

Study the example below:

mysql> create table test(id serial primary key, val int, cnt int);
Query OK, 0 rows affected (0.25 sec)

mysql> insert into test (val)
    -> select 10*rand()
    -> from (
    -> select 1 union all
    -> select 2 union all
    -> select 3 union all
    -> select 4 union all
    -> select 5 union all
    -> select 6 union all
    -> select 7 union all
    -> select 8 union all
    -> select 9 union all
    -> select 10
    -> ) dummy;
Query OK, 10 rows affected (0.06 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+------+------+
| id | val  | cnt  |
+----+------+------+
|  1 |    2 | NULL |
|  2 |    7 | NULL |
|  3 |    6 | NULL |
|  4 |    0 | NULL |
|  5 |    2 | NULL |
|  6 |    2 | NULL |
|  7 |    1 | NULL |
|  8 |    1 | NULL |
|  9 |    3 | NULL |
| 10 |    9 | NULL |
+----+------+------+
10 rows in set (0.00 sec)

mysql> create trigger cnt_absent_in_last_10 before insert on test
    -> for each row
    -> set new.cnt = (select 10 - count(distinct val) from (select * from test order by id desc limit 10) dummy);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into test (val)
    -> select 10*rand()
    -> from (
    -> select 1 union all
    -> select 2 union all
    -> select 3
    -> ) dummy;
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+------+------+
| id | val  | cnt  |
+----+------+------+
|  1 |    2 | NULL |
|  2 |    7 | NULL |
|  3 |    6 | NULL |
|  4 |    0 | NULL |
|  5 |    2 | NULL |
|  6 |    2 | NULL |
|  7 |    1 | NULL |
|  8 |    1 | NULL |
|  9 |    3 | NULL |
| 10 |    9 | NULL |
| 16 |    8 |    3 | -- absent in 1..10: 4,5,8
| 17 |    4 |    2 | -- absent in 2..16: 4,5
| 18 |    6 |    2 | -- absent in 3..17: 5,7
+----+------+------+
13 rows in set (0.00 sec)

mysql>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.