SQL Procedure (executed every second)

Question

SQL Procedure (executed every second)

Navigation

#1 by (3 votes)

2

Can anyone explain to me how this mysql command will work?

 SELECT @row := @row + 1 AS n_linha , m.* FROM
    (SELECT b.user_id as prox_user_id, t.auctionID, t.productID, t.auc_due_price+t.auc_plus_price AS prox_valor, t.auc_due_time, b.id as id_lance, t.auc_plus_time, t.auc_plus_price
    FROM bidbutler b
    inner join c_cron_tempo t on b.auc_id = t.auctionID
    where b.butler_status = 0 and (b.butler_bid-b.used_bids)>0
      and t.auc_due_time < GREATEST(LEAST(TRUNCATE(15 * RAND(),0),15),3)
    Order by b.auc_id, RAND()) m,(SELECT @row :=0) r;

Mostly the part of the RAND there, I could not understand.

I know that it runs on various RAND values, how could I also do it to run on a value set by me without RAND?

mysql sql

asked by anonymous 21.10.2017 / 14:48

1 answer

INNER JOIN + LEFT JOIN How to call asynchronous method in class constructor?

score 3 · Answer 1

A RAND() function returns a% with 0% to 0%. (example: float Multiplied by 15: 0.47806898642151524 )
A 7.171034796322728 function cuts a TRUNCATE() for a number of houses the second parameter. (example for float : TRUNCATE(7.171034796322728, 0) , because it leaves zero decimal places)
A function 7 returns the < in> minor of two numbers. (example for LEAST() : LEAST(7,15) )
A function 7 returns the < in> major of two numbers. (example for GREATEST() : GREATEST(7,3) )

Therefore, the command:

select GREATEST(LEAST(TRUNCATE(15 * RAND(),0),15),3)

It will return a random number between 3 and 15 .

Using the same rationale to change the range to, for example, between 1 and 5, multiply the result of 7 by 5, set the ceiling to RAND() to 5, and the minimum to LEAST() to 1:

select GREATEST(LEAST(TRUNCATE(5 * RAND(),0),5),1);

(Here's the SQL Fiddle example of the use of each function as well as followed runs of the above command to demonstrate the degree of randomness obtained: link )

Your complete query uses this method to check if the GREATEST() field is less than any number between 3 and 15.

If you want to set the number, remove all that block and replace it directly with the number you want to define, for example 10 :

...
and t.auc_due_time < 10
...