Tirage au sort aléatoire pondéré dans une requête SQL

Réponse acceptée !
salut

on va prendre un truc un peu moins aleatoire :

# de ligne         #coefficient
Ligne 1 :          20
Ligne 2 :          20
Ligne 3 :          60

mysql> CREATE DATABASE test
    -> ;
Query OK, 1 row affected (0.11 sec)

mysql> USE test
Database changedmysql> CREATE TABLE foo (id int unsigned auto_increment, ligne char(5), pourcent int, primary key(id));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO foo(ligne, pourcent) VALUES ("l1", 20), ("l2", 20), ("l3", 60);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM foo;
+----+-------+----------+
| id | ligne | pourcent |
+----+-------+----------+
|  1 | l1    |       20 |
|  2 | l2    |       20 |
|  3 | l3    |       60 |
+----+-------+----------+
3 rows in set (0.00 sec)

mysql> SELECT ligne, (SELECT SUM(pourcent) FROM foo AS b WHERE b.id <= a.id) FROM foo AS a;
+-------+---------------------------------------------------------+
| ligne | (SELECT SUM(pourcent) FROM foo AS b WHERE b.id <= a.id) |
+-------+---------------------------------------------------------+
| l1    |                                                      20 |
| l2    |                                                      40 |
| l3    |                                                     100 |
+-------+---------------------------------------------------------+
3 rows in set (0.00 sec)

ensuite, en bidouillant un peu, on arrive a faire ca :

mysql> SET @random = ROUND(RAND() * 100);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @random AS rand, (SELECT ligne FROM (SELECT id, ligne, (SELECT SUM(pourcent) FROM foo AS b WHERE b.id <= a.id) AS percent FROM foo AS a) AS sub WHERE percent > rand ORDER BY id ASC LIMIT 1) AS ligne;
+------+-------+
| rand | ligne |
+------+-------+
| 99   | l3    |
+------+-------+
1 row in set (0.00 sec)


chaque fois que j'ai tente de combiner les deux, mysql fout sa merde...