mysql 查询多条随机结果

gmyhbio 2012-12-31 03:02:56

三个字段：id name probability
probability初始默认全是1

probability值越大，随机出来的概率越高。

select * from kzmod_mapchoose order by probability*(SELECT FLOOR( MAX(probability) * RAND()) FROM kzmod_mapchoose ) desc limit 8;

我改了一条数据的值为10，它就一直都出现了。。
怎么改进？还有就是，怎样知道概率值大于平均值多少时，这条数据会百分百出现？

...全文

267 12 打赏收藏转发到动态举报

写回复

用AI写文章

12 条回复

切换为时间正序

请发表友善的回复…

发表回复

gmyhbio 2013-01-08

打赏
举报

谢谢斑竹的耐心解答，要的就是这个效果。太NB了。

ACMAIN_CHM 2013-01-04

打赏
举报

楼主，你需要定义一个公式，probability的值对应的出现概率是什么，这个是你自己的需求定义，别人无法帮你的。比如假设 1000 条记录，它们的probability值从1,2,3..1000 那每条记录你期望出现的概率是多少？

ACMAIN_CHM 2013-01-04

打赏
举报

无法理解楼主的需求到底是什么了。你就直接 select * from kzmod_mapchoose order by pow(probability,1/80)*RAND() desc limit 8;

gmyhbio 2013-01-04

打赏
举报

引用 9 楼 ACMAIN_CHM 的回复:

楼主，你需要定义一个公式，probability的值对应的出现概率是什么，这个是你自己的需求定义，别人无法帮你的。比如假设 1000 条记录，它们的probability值从1,2,3..1000 那每条记录你期望出现的概率是多少？

就是随机取数据阿，这个期望的概率我怎么说？只是概率值能适当影响一下被随机到的概率而已。不要经常出现就好了。最好的效果。就是，即使概率值到了1000也不一定能次次都出现最好了。之前你们两人给的sql。。概率值设到10就几乎次次都能出现了。。其他的是1的，基本没出现的几率了。这怎么行。

gmyhbio 2013-01-03

打赏
举报

引用 7 楼 ACMAIN_CHM 的回复:

楼主的需求是什么？比如 100 个数，99个1 和一个 10 每次选 10 个，一共选 10 次楼主希望那个 10 出现的次数或者概率是多少？所谓的 "出现概率高" 是个什么概念？高到多少？当这个probability值从10变成1000时，楼主期望它出现的概率是多少？

以你的条件，选10次，概率为10的出现1-2次就好了。。概率100就4-5次吧。1000就几乎次次都有了。 10个里，一半以上的结果从平均概率往下的数据里选。。

ACMAIN_CHM 2013-01-03

打赏
举报

我改了一条数据的值为10，它就一直都出现了。。怎么改进？还有就是，怎样知道概率值大于平均值多少时，这条数据会百分百出现？ RNAD 的的值平均等概率分布在 0-1 之间。你的 10 * RAND() 的结果会平均分布在 0-10 之间。也就是说有 90% 的概率这个结果是 >1 的。你到底希望的结果是什么样？建议举例说明。

ACMAIN_CHM 2013-01-03

打赏
举报

楼主的需求是什么？比如 100 个数，99个1 和一个 10 每次选 10 个，一共选 10 次楼主希望那个 10 出现的次数或者概率是多少？所谓的 "出现概率高" 是个什么概念？高到多少？当这个probability值从10变成1000时，楼主期望它出现的概率是多少？

gmyhbio 2013-01-03

打赏
举报

多查询几次就能看是不是100%出现的。你们2个SQL，概率为1的出现的几率太小了。我想的是，这100条数据，都是消耗金币，用户来增加概率值的。要是增加到10几乎100%出现了。那还有什么意义？只要8条结果，，大多数的结果还是在平均值概率值以下的，只有几条会是概率值高的。比如 1 11 2 34 1 4 5 8

gmyhbio 2013-01-03

打赏
举报

CREATE TABLE `kzmod_mapchoose` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`mapname`  varchar(60) NOT NULL ,
`probability` int(11) NOT NULL DEFAULT 1 ,
PRIMARY KEY (`id`, `mapname`)
);

INSERT INTO kzmod_mapchoose(mapname) VALUES ('kztag_compound');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kztag_depot');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kztag_island');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kztag_mout');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kztag_outerspace');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kztag_race');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kztag_sacred');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kztag_shadypond');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kztag_skyscrapers');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kztag_towers');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kztag_zone');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_4youreyesonly');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_abstrangel');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_afterdark');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_aim_flareworld');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_asteroid_field');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_beach');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_beginnerblock');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_bhop_angkor');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_bhop_arctic');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_bhop_backalley');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_bhop_backport');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_bhop_bathhouse');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_bhop_beginnersluck');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_bhop_cartooncastle');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_bhop_chillhop');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_bhop_dementia');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_bhop_femto');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_bhop_goldhop');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_bhop_hangman');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_bhop_kashgar');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_bhop_ocean');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_bhop_ravine');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_bhop_shrubhop');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_bhop_tiles');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_bhop_volcanohop');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_bhop_watertemple');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_bhop_wtf');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_brooklyn');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_cathedral');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_cathedral_ez');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_caulis');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_caverns_deep');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_cellblock');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_cityswingers');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_cliffhanger');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_cloudwalker');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_cmfrb');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_columns_deluxe');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_computer');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_cookbook');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_cookbook_ez');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_coop_minis');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_crate_delight');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_cube');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_dance');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_datatransfer');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_date');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_date2');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_deathrow');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_debod');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_egyptian_halls');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_fortress');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_fortress_ez');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_fragmania');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_freezing_ridge');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_frozen');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_ghosttown_ez');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_highwire');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_hillside');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_invasion');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_karnak');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_linejump');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_longjumps');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_man_madness2');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_mars');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_mausolus');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_maze_wickedgarden');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_metro');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_minimania');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_mix_journeys');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_mix_piraraa');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_mix_sickness');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_moria');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_oakvalley');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_oilrig');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_panama');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_powerjump');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_quadrathon');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_redemption');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_refinery_east');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_refinery_ez');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_romeo2juliet');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_sandyhill');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_sheol');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_silo');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_silo_ez');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_sliderace_city');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_spiritblock');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_station');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_still');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_strafe_protraining');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_stranded');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_surfcrazy');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_surf_protraining');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_testlab');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_thehills');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_tombraider');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_tradeblock');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_trippin');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_trippin_sp');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_tunnels');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_tutorial');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_utopia');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_xmas2010');
INSERT INTO kzmod_mapchoose(mapname) VALUES ('kz_xtremeblock');

两位大哥的代码，各有千秋，不过貌似你们的排序都排反了？概率值高的，全排最末尾去了。 2楼大哥的是没有随机性。 1楼大哥的好一些，但是20的概率基本就能次次都出了。

|  20 | kz_bhop_arctic        |          10 |
|   5 | kztag_outerspace      |          12 |
|  23 | kz_bhop_bathhouse     |          12 |
|  21 | kz_bhop_backalley     |          25 |
|   3 | kztag_island          |          10 |
|  12 | kz_4youreyesonly      |          15 |
|   8 | kztag_shadypond       |          12 |
|  22 | kz_bhop_backport      |          20 |
|  19 | kz_bhop_angkor        |          30 |
|  17 | kz_beach              |          45 |

最佳效果最好是能混杂一些概率是1的。。这个概率值只是多多少少很影响一些出现的几率。但是不要这样夸张的几乎次次都出现。

欢乐的尼美 2012-12-31

打赏
举报

学习。狼头大哥好长的代码。。。

ACMAIN_CHM 2012-12-31

打赏
举报

select * from kzmod_mapchoose a, (select sum(probability)*RAND() as k from kzmod_mapchoose) b where (select sum(probability) from kzmod_mapchoose where probability>=a.probability)> b.k order by (select sum(probability) from kzmod_mapchoose where probability>=a.probability) desc limit 1

WWWWA 2012-12-31

打赏
举报

SELECT a.* FROM kzmod_mapchoose a INNER JOIN (SELECT id,probability*(SELECT FLOOR(MAX(probability) * RAND()) FROM kzmod_mapchoose) AS aa FROM kzmod_mapchoose) c ON a.id=c.id ORDER BY c.aa