如何动态地根据项目重要性分配权重？

Question

I'm trying to figure out a method that I could use to build a system that would assign weights to independent objects in a table for random weighted sorting - based on the importance of that item. If a scale were on 0-9, for instance. If you assigned four items 3, 5, 9, 1, then the system would be able to sort through those numbers, and build the list so that each weight adds up to 1 total between the four objects. How would you go about sorting these items?

At the moment, this is what I have for SQL:

CREATE TABLE IF NOT EXISTS `block_types` (
    `ID` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `BlockTypeUID` varchar(35),
    `Name` varchar(20),
    `Weight` float,
    `Turns` int(11),
    `Score` int(11),
    `Hitpoints` int(20),
    `Rarity` int(11),
    `Multiplier` int(1),
    `Effect` int(11),
    `Area` int(11),
    PRIMARY KEY (`ID`)
) DEFAULT COLLATE=utf8_general_ci;

Weight is a float from 0.0001 to 1, and at the moment I'm basically assigning it manually through a simple input form. The idea is that Rarity would handle storing the 0-9, and be used to automatically calculate the new weight based on all other entries - so that the total sum of the table equaled 1.

Answer 1

EDIT: I read the bottom part of your question.

This slightly messy query makes all of the weight fields in your table add up to 1 based on rarity, proportionally. (It will probably be more like .9999999998)

UPDATE block_types SET Weight=Rarity/(SELECT SUM(Rarity) AS r FROM (SELECT Rarity FROM block_types) AS bt);

For example, if there are two rows, one with rarity 40 and the other with rarity 80, you'd end up with weight .3333 and .6666 (40 being 1/3 of the total 120, and 80 being 2/3).

Hope that helps.