TABLE Name = `il`
Index Series Round
1 A ?
2 A
3 A
4 B
5 B
6 C
7 A
8 A
9 B
10 C
例如上面這樣的資料結構，想要分配Round號碼1~5
但希望同一個Series的資料避免分配到同一個Round
目前使用的SQL語法如下：
SET @id=0;
UPDATE `il` JOIN (SELECT `Index`,(1+(@id:=@id+1)%5) AS `Round`
FROM (SELECT `Index` FROM `il` ORDER BY `Series`,RAND()) AS `sb`)
AS `sc` USING (`Index`) SET `il`.`Round`=`sc`.`Round`;
但是這樣的語法出現一個問題，例如在這個範例中
Series B 永遠只能分配到Round 2,3,4, Series C則只能分配到Round 1,5
目前想到的改進語法：
SET @id=0;
UPDATE `il` JOIN (SELECT `Index`,@id:=@id+1 AS `Round`
FROM (SELECT `Index` FROM `il` ORDER BY `Series`,RAND()) AS `sb`)
AS `sc` USING (`Index`) SET `il`.`Round`=`sc`.`Round`;
SET @id=0;
UPDATE `il` SET `Round`=(@id:=@id+1) WHERE `Round` BETWEEN 1 AND 5
ORDER BY RAND();
SET @id=0;
UPDATE `il` SET `Round`=(@id:=@id+1) WHERE `Round` BETWEEN 6 AND 10
ORDER BY RAND();
可是這樣的寫法資料庫如果大起來就要跑上一陣子
想請問有沒有比較快的作法？
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 122.116.180.163
※ 編輯: SmallBeeWayn 來自: 122.116.180.163 (06/15 00:59)