MySQL balanced-distributed query?

Question

Hi guys,

Suppose I have a table like this:

hseid | projcode | bulidarea | room | hall | floor | totalfloor | price

0 | 1 | 100 | 1 | 1 | 9 | 25 | 100

1 | 2 | 99  | 1 | 1 | 9 | 25 | 100

2 | 2 | 101 | 1 | 1 | 9 | 25 | 100

3 | 4 | 110 | 1 | 1 | 9 | 25 | 100

4 | 3 | 130 | 1 | 1 | 9 | 25 | 100

5 | 1 | 95  | 1 | 1 | 9 | 25 | 100

6 | 4 | 98  | 1 | 1 | 9 | 25 | 100

7 | 3 | 101 | 1 | 1 | 9 | 25 | 100

Note that hseid represented as unique building, projcode represents different property project developers, and the rest fields are not important.

Now to balance the argument among property project developers, I should make the result looks like this:

hseid | projcode | bulidarea | room | hall | floor | totalfloor | price

0 | 1 | 100 | 1 | 1 | 9 | 25 | 100

1 | 2 | 99  | 1 | 1 | 9 | 25 | 100

4 | 3 | 130 | 1 | 1 | 9 | 25 | 100

3 | 4 | 110 | 1 | 1 | 9 | 25 | 100

5 | 1 | 95  | 1 | 1 | 9 | 25 | 100

2 | 2 | 101 | 1 | 1 | 9 | 25 | 100

7 | 3 | 101 | 1 | 1 | 9 | 25 | 100

6 | 4 | 98  | 1 | 1 | 9 | 25 | 100

Basically the rule is:

• Rotate each projcode
• In each projecode, randomly pick one which is not already showed.

How do I implement this in MySQL?

Thanks a lot.

Answer 1

I can't understand the rule but I think to transform the first table to the second one you can use Cursors in a procedure

Answer 2

I am not clear your rule or conditional field. However, if you are attempting to randomly select a project code based on showed, append the ORDER BY RAND() LIMIT 1 to your query. This will select the top one, with several assumptions about your table.

SELECT projcode FROM your_table WHERE showed_column = 0 ORDER BY RAND() LIMIT 1