Shulou(Shulou.com)05/31 Report--

In this article Xiaobian for you to introduce in detail "SQL random number case analysis", the content is detailed, the steps are clear, the details are handled properly, I hope this "SQL random number example analysis" article can help you solve your doubts, following the editor's ideas slowly in-depth, let's learn new knowledge.

To get a random number, there are actually many ways to write SQL. For example, if you limit the range of data to 7-12, you can write like this.

Mysql > SELECT FLOOR (7 + (RAND () * 6))

+-+

| | FLOOR (7 + (RAND () * 6)) | |

+-+

| | 10 |

+-+

1 row in set

For example, if the limited data range is less than 100, it can be written like this.

Mysql > select truncate (round (rand (), 2) * 100jin0)

+-+

| | truncate (round (rand (), 2) * 100Pol 0) | |

+-+

| | 78 |

+-+

Of course, this is only the most basic implementation, and the influence of seed function has not been taken into account.

Let's put aside the complexity of the implementation for a moment and take a look at the different implementations that combine some scenarios. I temporarily invented several concepts, but the meanings should be the same.

Average random number

This is a seemingly random but controllable way of distributing data.

For example, 1: 100 I generate 10 numbers, I can divide every 10 numbers into a group. Each group was randomly assigned.

In this way, I generate a random sequence for 1: 10, for example, if the first random number is 2, I choose a number from 20 to 29, and the next number is 5, then I choose a number from 50 to 59.

In this way, the distribution of the data is controllable, but the random characteristic is guaranteed.

One-time random number

This kind of random number is like Shaanxi oil spilled noodles, simple and fast, can be done with one spoonful of oil. If I need 10 numbers, then I generate 10 random numbers at a time.

It looks like it's a little difficult to implement, but it's okay to use rand () and limit.

Insert 8 pieces of data.

Mysql > insert into random values

1), (2), (40), (30), (20), (9), (15), (21)

Query OK, 8 rows affected

Records: 8 Duplicates: 0 Warnings: 0

The data obtained by default is orderly.

Mysql > select * from random

+-- +

| | id |

+-- +

| | 1 |

| | 2 |

| | 40 |

| | 30 |

| | 20 |

| | 9 |

| | 15 |

| | 21 |

+-- +

8 rows in set

Use rand to get a random sequence.

Mysql > select * from random order by rand ()

+-- +

| | id |

+-- +

| | 1 |

| | 2 |

| | 30 |

| | 20 |

| | 40 |

| | 21 |

| | 15 |

| | 9 |

+-- +

8 rows in set

If you need to intercept, you can use limit.

Mysql > select * from random order by rand () limit 4

+-- +

| | id |

+-- +

| | 30 |

| | 21 |

| | 9 |

| | 20 |

+-- +

4 rows in set

Dynamic random number

This kind of random number is the most expensive and needs to be calculated repeatedly. In short, the uncertainty is much higher, but the randomness is greater.

For example, to take 10 out of 100 numbers, every time we take out a number, we need to exclude it and re-take it from the list, so that it is possible if it is a continuous number 1 / 2 / 3.

Let's briefly simulate the lottery process through SQL.

Initialize the number of tables and data.

Create table lucky_money (id int primary key,money int,status smallint)

The stored procedure is as follows:

Delimiter $$

Create procedure proc_init ()

Begin

Declare

Init_data integer default 1

While init_data select avg (money) from lucky_money

+-+

| | avg (money) |

+-+

| | 360.0000 |

+-+

1 row in set

Read here, this "SQL random number example analysis" article has been introduced, want to master the knowledge of this article also need to practice and use to understand, if you want to know more about the article, welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.