Mysql Query record massimi in formato n * 2 con intervallo

facendo riferimento alla mia domanda precedente, sto facendo questa nuova domanda con un problema simile.

Sto records tabelle come questa

 ---- --------- ------ id name points ---- --------- ------ 1 aaaa 90 2 bbbb 87 3 cccc 90 4 dddd 80 5 eeee 86 6 ffff 82 7 gggg 87 8 hhhh 85 10 iiii 86 11 iiii 86 12 iiii 86 13 iiii 86 14 iiii 87 15 iiii 73 16 iiii 86 17 iiii 73 18 hhhh 85 19 hhhh 73 20 hhhh 73 21 hhhh 70 22 hhhh 65 23 hhhh 70 24 hhhh 50 

forma la tabella, voglio select i record con la seguente condizione

  1. Per il primo record massimo, i punti più alti saranno in cima. (Ma solo un record). Ho due record con il punto più alto 90, Qui dovrebbe essere visualizzato quello recente (id massimo)

  2. Dal secondo record in poi, i campi dovrebbero essere recuperati nel formato di (n * 2). Voglio dire, per i secondi record massimi posso lasciare cadere 4 valori (2 * 2 = 4) con un intervallo compreso tra il secondo record massimo-10. cioè nella tabella seleziono solo 4 record recenti (Maximum id) con nell'intervallo da 77 a 87 (87-10 = 77). ora il 3o record massimo, dovrebbe essere minore o uguale a 76 (77-1) cioè nella tabella era 73. analogamente per il 3o massimo, posso permettere che i record 3 * 2 = 6 cadano entro 63-73 (73 -10 = 63) .. e così via ..

Attualmente sto usando la risposta dalla mia altra domanda

 SELECT id, name, points FROM ( SELECT t.* , @n := IF(@prev_points != points, @n + 1, @n) AS n , @row := IF(@prev_points != points, 1, @row + 1) AS row , @prev_points := points FROM t , (SELECT @prev_points := null, @n := 1, @row := 0) var_init_subquery ORDER BY points DESC, id DESC ) sq WHERE row <= CASE WHEN n = 1 THEN 1 ELSE n * 2 END ; 

Ma limita a solo uno stesso record. Esiste la possibilità di utilizzare il count o qualsiasi altra function rilevante in questa query in base alla necessità. Spero che tu mi aiuti.

L'output dovrebbe assomigliare a questo,

 ---- --------- ------ id name points ---- --------- ------ 3 cccc 90 -- maximum (only one) 14 iiii 87 | 7 gggg 87 | -- 2nd maximum (allow 2*2 =4 only) 2 bbbb 87 | 16 iiii 86 | 20 hhhh 73 | 19 hhhh 73 | -- 3rd maximum (allow 3*2 =6 only) 17 iiii 73 | 14 iiii 73 | 23 hhhh 70 | and so on for 4th and 5th 21 hhhh 70 | 24 hhhh 50 | -- 4th 4*2 = 8 

 /*Sample data*/ CREATE TABLE t (`id` int, `name` varchar(4), `points` int) ; INSERT INTO t (`id`, `name`, `points`) VALUES (1, 'aaaa', 90), (2, 'bbbb', 87), (3, 'cccc', 90), (4, 'dddd', 80), (5, 'eeee', 86), (6, 'ffff', 82), (7, 'gggg', 87), (8, 'hhhh', 85), (10, 'iiii', 86), (11, 'iiii', 86), (12, 'iiii', 86), (13, 'iiii', 86), (14, 'iiii', 87), (15, 'iiii', 73), (16, 'iiii', 86), (17, 'iiii', 73), (18, 'hhhh', 85), (19, 'hhhh', 73), (20, 'hhhh', 73), (21, 'hhhh', 70), (22, 'hhhh', 65), (23, 'hhhh', 70), (24, 'hhhh', 50) ; 

 /*Query*/ ( SELECT id, name, points, 'maximum (only one)' AS maximum, 'just the max' AS group_range FROM t ORDER BY points DESC, id DESC LIMIT 1 ) UNION ALL ( SELECT id, name, points, CONCAT(n, ' maximum'), CONCAT('range from ', group_max, ' to ', group_max - 10) FROM ( SELECT t.* , @n := IF(points < @group_max - 10, @n + 1, @n) AS n , @group_max := IF(@n != @prev_n, @group_max - 11, @group_max) AS group_max , @row := IF(@n != @prev_n, 1, @row + 1) , IF(@row > @n * 2, 0, 1) AS select_it , @prev_n := @n FROM t , (SELECT @prev_n := 2, @max := points, @group_max := (SELECT points FROM t WHERE points != (SELECT MAX(points) FROM t) ORDER BY points DESC LIMIT 1), @n := 2, @row := 0 FROM t ORDER BY points DESC LIMIT 1) var_init_subquery WHERE points != @max ORDER BY points DESC, id DESC ) sq WHERE select_it = 1 ); 

Nota, hai incasinato il risultato desiderato un po 'credo. Aggiunte due colonne per dimostrarlo 🙂

 /*Result*/ | ID | NAME | POINTS | MAXIMUM | GROUP_RANGE | |----|------|--------|--------------------|---------------------| | 3 | cccc | 90 | maximum (only one) | just the max | | 14 | iiii | 87 | 2 maximum | range from 87 to 77 | | 7 | gggg | 87 | 2 maximum | range from 87 to 77 | | 2 | bbbb | 87 | 2 maximum | range from 87 to 77 | | 16 | iiii | 86 | 2 maximum | range from 87 to 77 | | 20 | hhhh | 73 | 3 maximum | range from 76 to 66 | | 19 | hhhh | 73 | 3 maximum | range from 76 to 66 | | 17 | iiii | 73 | 3 maximum | range from 76 to 66 | | 15 | iiii | 73 | 3 maximum | range from 76 to 66 | | 23 | hhhh | 70 | 3 maximum | range from 76 to 66 | | 21 | hhhh | 70 | 3 maximum | range from 76 to 66 | | 22 | hhhh | 65 | 4 maximum | range from 65 to 55 | | 24 | hhhh | 50 | 5 maximum | range from 54 to 44 | 
  • dimostrazione dal vivo

Aggiornamento dopo chiarimenti:

 ( SELECT id, name, points, 'maximum (only one)' AS maximum, 'just the max' AS group_range FROM t ORDER BY points DESC, id DESC LIMIT 1 ) UNION ALL ( SELECT id, name, points, CONCAT(n, ' maximum'), CONCAT('range from ', group_max, ' to ', group_max - 10) FROM ( SELECT t.* , @n := IF(points < @group_max - 10, @n + 1, @n) AS n , @group_max := IF(@n != @prev_n, points, @group_max) AS group_max , @row := IF(@n != @prev_n, 1, @row + 1) , IF(@row > @n * 2, 0, 1) AS select_it , @prev_n := @n FROM t , (SELECT @prev_n := 2, @max := points, @group_max := (SELECT points FROM t WHERE points != (SELECT MAX(points) FROM t) ORDER BY points DESC LIMIT 1), @n := 2, @row := 0 FROM t ORDER BY points DESC LIMIT 1) var_init_subquery WHERE points != @max ORDER BY points DESC, id DESC ) sq WHERE select_it = 1 ); 

 | ID | NAME | POINTS | MAXIMUM | GROUP_RANGE | |----|------|--------|--------------------|---------------------| | 3 | cccc | 90 | maximum (only one) | just the max | | 14 | iiii | 87 | 2 maximum | range from 87 to 77 | | 7 | gggg | 87 | 2 maximum | range from 87 to 77 | | 2 | bbbb | 87 | 2 maximum | range from 87 to 77 | | 16 | iiii | 86 | 2 maximum | range from 87 to 77 | | 20 | hhhh | 73 | 3 maximum | range from 73 to 63 | | 19 | hhhh | 73 | 3 maximum | range from 73 to 63 | | 17 | iiii | 73 | 3 maximum | range from 73 to 63 | | 15 | iiii | 73 | 3 maximum | range from 73 to 63 | | 23 | hhhh | 70 | 3 maximum | range from 73 to 63 | | 21 | hhhh | 70 | 3 maximum | range from 73 to 63 | | 24 | hhhh | 50 | 4 maximum | range from 50 to 40 |