2010年1月5日 星期二

[SQL]Assign sequential numbers randomly for a particular group of records

Spammer Tang asked me, "How?"
I said, "Why don't you just use PHP?"

Anyway, I got a solution by just using SQL.
Here is the example records:
[table `team`]
id name dept ticket
1 Samuel 1 0
2 Patrick 1 0
3 Arthur 1 0
4 Martin 1 0
5 Ozetta 3 0
6 Alice 2 0
7 Bob 2 0
8 Carol 2 0
9 Dave 2 0
10 Mallory 3 0
11 Steve 3 0
12 Victor 3 0

Now we want to assign 1, 2, 3, 4 randomly on the ticket column where the dept code is 1 (first 4 records). Here is my solution:
SET @dept = 1;
DROP TABLE IF EXISTS temp_map;
CREATE TABLE temp_map(p INT NOT NULL AUTO_INCREMENT PRIMARY KEY, q INT NOT NULL);
INSERT INTO temp_map (SELECT NULL, id FROM team WHERE dept = @dept);
DROP TABLE IF EXISTS temp_ticket;
CREATE TABLE temp_ticket(u INT NOT NULL AUTO_INCREMENT PRIMARY KEY, v INT NOT NULL);
INSERT INTO temp_ticket (SELECT NULL, p FROM temp_map ORDER BY RAND());
UPDATE team a SET ticket = (SELECT c.v FROM temp_map b, temp_ticket c WHERE a.id = b.q AND b.p = c.u) WHERE dept = @dept;
DROP TABLE temp_map; DROP TABLE temp_ticket;
First we define which dept we are going to assign tickets. Actually this can also be done by php, asp or whatever easily, depends on your environment.

Then we create two temporary table for mapping. The first one is to map the IDs of the original table to a new ascending sequence. This is because the IDs of the original table with the same dept code may not continuous. For example, if the dept code is 3, then the mapping will be like this:
p q
1 5
2 10
3 11
4 12

The second table generates a random sequence, by using ORDER BY RAND(). You may find other faster or efficient way to do this. Here is one of the possible result:
u v
1 3
2 4
3 1
4 2
(v value may be different.)

Finally we update the tickets from the original table given the above two tables. Note that there is a where clause after the sub-query. This is to prevent updating other records' ticket to zero or null.

The last line is just to remove the temporary tables. You may ignore this.

Please feel free to leave comments or give any suggestions to this solution.

沒有留言:

張貼留言