SQL - query by dynamic weights

I have a database with users that have a list of items.

Simple design:

Users:

UserStuff | UserID

ItemsList:

itemID | UserID

Each user has a list of items. An Item can belong to many users (same itemID, different userID).

Now I want to give weights to the items dynamically (calculated at runtime, not something constant that can be stored in the database) and then Select the users with t开发者_如何学运维he highest weights according to their Items.

Can I give the weight to the items in an SQL query, and then do the calculation ?

I'm trying to avoid looping through the database and calculating the users' weights.

Edit:

example:

UserStuff | UserID

blah1 | 1

blah2 | 2

itemID | UserID

1 | 1

1 | 2

2 | 1

3 | 1

4 | 2

5 | 1

wieghts:

itemId = 1 = 15 itemID = 2 = 10

UserID1 total weight = 25

UserID2 total weight = 10

Create a temporary table of weights, with your dynamic values:

CREATE TEMPORARY TABLE weights(item_id integer, weight integer);
CREATE TABLE
INSERT INTO weights VALUES (1, 10);  -- Inserting weights
INSERT 0 1
INSERT INTO weights VALUES (2, 1);   -- Inserting weights
INSERT 0 1
...

Create a view to map items to weights:

CREATE TEMPORARY VIEW items_weights AS 
       SELECT il.itemID, il.UserID, w.weight FROM
              ItemsList il JOIN weights w 
              ON il.itemID=w.item_id;

Select the highest-weighted users:

SELECT UserId, COUNT(weight) FROM items_weights
       GROUP BY UserId ORDER BY COUNT(weight) DESC;