作者SmallBeeWayn (喵喵叫的蜜蜂猫)
看板Database
标题[SQL ] 将资料项有条件平均分配
时间Tue Jun 15 22:44:25 2010
资料库版本: MySQL 5.1 MyIASM
网页是PHP 5.2 不过不太希望用到就是了
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
1F：→ grence:是要随机安排又不重复？ 06/15 23:20