MariaDB [AB_FORUMS]> select id, username, reputation as rep, warnings_last_year as warns, CAST(warnings_last_year * reputation AS UNSIGNED) as sort from users order by warnings_last_year * reputation desc limit 30; +--------+------------------------+---------+-------+-------+ | id | username | rep | warns | sort | +--------+------------------------+---------+-------+-------+ | 79 | AGRESSOR | 148.724 | 94 | 13980 | | 7206 | энди | 52.7149 | 162 | 8540 | | 2631 | Bredonosec | 90.7033 | 74 | 6712 | | 190 | KILLO | 53.042 | 110 | 5835 | | 143 | MD | 117.415 | 46 | 5401 | | 30728 | Meskiukas | 55.1604 | 90 | 4964 | | 3697 | kirill111 | 52.7529 | 89 | 4695 | | 3796 | Бяка | 44.6159 | 96 | 4283 | | 6412 | alex_ii | 55.2964 | 54 | 2986 | | 34 | Старый | 21.0203 | 99 | 2081 | | 10005 | tramp_ | 35.0353 | 52 | 1822 | | 1457 | Vale | 42.4923 | 41 | 1742 | | 2545 | GOGI | 78.5462 | 22 | 1728 | | 29694 | Кот_да_Винчи | 33.2655 | 46 | 1530 | | 2465 | Sheradenin | 25.0652 | 59 | 1479 | | 8136 | Bryansk Eagle | 54.3764 | 26 | 1414 | | 6373 | spam_test | 25.7648 | 54 | 1391 | | 3297 | Сергей-4030 | 57.5368 | 24 | 1381 | | 843 | Fakir | 99.6152 | 13 | 1295 | | 853 | russo | 61.785 | 19 | 1174 | | 2289 | Tolka | 23.8161 | 46 | 1096 | | 105432 | TEvg-2 | 12.2568 | 88 | 1079 | | 32 | Serg Ivanov | 23.1862 | 46 | 1067 | | 1280 | anybody | 27.7595 | 36 | 999 | | 7787 | Scar | 18.5754 | 49 | 910 | | 81 | Kuznets | 36.2875 | 25 | 907 | | 68 | hcube | 57.0313 | 15 | 855 | | 28917 | majera | 19.8319 | 35 | 694 | | 7545 | slab105 | 16.2362 | 39 | 633 | | 4655 | Анатолий | 52.2923 | 12 | 628 | +--------+------------------------+---------+-------+-------+ 30 rows in set (0.04 sec)
MariaDB [AB_FORUMS]> select id, username, warnings_last_year as warns, reputation as rep from users order by warnings_last_year desc limit 30; +--------+----------------------------+-------+-----------+ | id | username | warns | rep | +--------+----------------------------+-------+-----------+ | 24672 | Andru | 200 | -0.630612 | | 7206 | энди | 162 | 52.7149 | | 61338 | ccsr | 151 | -16.435 | | 86745 | Lamort | 145 | -25.315 | | 84463 | Lazy Rider | 137 | -25.1162 | | 86449 | ADI | 132 | -24.6197 | | 106359 | Pavel2233 | 130 | -32.5067 | | 105560 | Lumen | 130 | -20.7735 | | 190 | KILLO | 110 | 53.042 | | 6884 | Militarist | 107 | -10.0561 | | 34 | Старый | 99 | 21.0203 | | 62004 | preodol | 98 | -14.9594 | | 3796 | Бяка | 96 | 44.6159 | | 79 | AGRESSOR | 94 | 148.724 | | 30728 | Meskiukas | 90 | 55.1604 | | 3697 | kirill111 | 89 | 52.7529 | | 105432 | TEvg-2 | 88 | 12.2568 | | 3352 | Jerard | 80 | 4.86961 | | 2631 | Bredonosec | 74 | 90.7033 | | 105853 | NDMG | 71 | -3.35559 | | 106593 | Студербеккер74 | 69 | -13.9463 | | 42250 | antifa | 68 | -13.9849 | | 8225 | Памятливый45 | 66 | -14.835 | | 46596 | sasza | 61 | -10.6168 | | 106198 | NeVelyuroff | 60 | -6.88184 | | 106420 | kuzmjy | 59 | 5.27997 | | 2465 | Sheradenin | 59 | 25.0652 | | 1995 | x-Merlin | 58 | 6.14456 | | 6412 | alex_ii | 54 | 55.2964 | | 6373 | spam_test | 54 | 25.7648 | +--------+----------------------------+-------+-----------+ 30 rows in set (0.01 sec)
MariaDB [AB_FORUMS]> select u.id, u.username, sum(score) as sum, count(*) as posts from posts p left join users u on u.id = p.poster_id where score >= 10 and posted > unix_timestamp() - 86400*365 group by u.id order by sum(score) desc limit 30; +--------+------------------------+------+-------+ | id | username | sum | posts | +--------+------------------------+------+-------+ | 102920 | johnkey68 | 9315 | 658 | | 79 | AGRESSOR | 5477 | 392 | | 29124 | Beholder44 | 5220 | 397 | | 84219 | Dem333 | 4954 | 363 | | 7206 | энди | 4772 | 335 | | 124 | U235 | 4765 | 348 | | 26763 | korneyy | 4472 | 324 | | 2427 | Беня | 4427 | 340 | | 26729 | yacc | 3836 | 274 | | 1686 | Алдан-3 | 3509 | 256 | | 30131 | Novice1975 | 3500 | 201 | | 29694 | Кот_да_Винчи | 3495 | 259 | | 5420 | Barbarossa | 3335 | 225 | | 10000 | Balancer | 3289 | 246 | | 7659 | V.Stepan | 3171 | 229 | | 7367 | wert | 2973 | 206 | | 27750 | Android | 2445 | 173 | | 26380 | Massaraksh | 2394 | 170 | | 29000 | ahs | 1897 | 145 | | 84520 | ChernomorStelth | 1892 | 134 | | 77352 | baroque | 1688 | 124 | | 2012 | Bell | 1687 | 124 | | 2631 | Bredonosec | 1562 | 122 | | 28889 | Sergofan | 1555 | 115 | | 64854 | dmirg78 | 1514 | 111 | | 4471 | xab | 1309 | 92 | | 32 | Serg Ivanov | 1241 | 96 | | 1579 | RighteouS | 1230 | 95 | | 85175 | Вымпел2011 | 1199 | 81 | | 64 | gals | 1198 | 91 | +--------+------------------------+------+-------+ 30 rows in set (0.99 sec)
MariaDB [AB_FORUMS]> select id, username, title from users where title <> ''; +--------+---------------+-------------------------------+ | id | username | title | +--------+---------------+-------------------------------+ | 34 | Старый | Брюзгливый хам | | 46 | muxel | Энтузиаст реактивного движения| | 72 | Zeus | Динамик | | 81 | Kuznets | Клерк-старожил | | 145 | au | | | 519 | Diadia_Sidor | разгонный блок космофорума | | 843 | Fakir | BlueSkyDreamer | | 1037 | volk959 | коммофоб | | 1457 | Vale | Сальсолёт | | 2289 | Tolka | Марвин | | 2450 | 7-40 | астрофизик | | 2455 | Генерал Абрамс| Сторожил | | 3297 | Сергей-4030 | исключающий третье | | 3796 | Бяка | имитатор знатока | | 5420 | Barbarossa | Любитель тунисских тётков | | 5788 | marata | Вахтер форумный | | 7206 | энди | двумерный | | 8099 | Tzvk | астрофизик | | 11172 | Tais2 | Любоф Балансера | | 21040 | Домовой | модератор | | 21761 | Murkt | Pythoneer | | 25284 | Capt(N) | Старожил | | 25833 | Вованыч_1977 | Местный | | 26052 | D.Vinitski | филин-стратег | | 31925 | AXT | инженер вольнодумец | | 84069 | BalaBOT | vox Dei | | 107078 | eto ya | 39rus | +--------+---------------+-------------------------------+ 27 rows in set (0.01 sec)