SQL QUERY：如何在表中为不同的行分配不同的值，以便在另一个表中分配不同的行？

Question

我正在开发一个类似Risiko！的游戏的桌面应用程序，它基于一些数据库程序。

我需要为每个玩家分配一系列不相等的，分配给其他玩家的行。

我已经试过这样的事情，但不工作：

`create or replace PROCEDURE "giveterritory" 
    (idpartita NUMBER,nr_partecipanti number) 
    is 

begin 

DECLARE 

CURSOR cur_file IS 

SELECT * 
FROM Player 
WHERE idgame = Player.idgame ; 

var_cur cur_file%ROWTYPE; 

cont number ; 
nr_territories_gioc_prec number ; 
G1_Terr int ; 
G2_Terr int ; 
G3_Terr int ; 
G1_ID number; 
G2_ID number; 
G3_ID number; 
begin 

cont:=0 ; 

for var_cur in cur_file 

loop 

cont := cont + 1 ; 

    update territory 
    set Player_owner = var_cur.id_Player 
    where ROWNUM = MOD(MOD (number_RANDOM(),42) ,ROWNUM); 
G1_Terr := var_cur.number_territories_tot ; -- mi salvo i territories del primo Player 
G1_ID := var_cur.id_Player ; 
end if ; 
    if (cont=2) then 


    update territory 
    set Player_owner = var_cur.id_Player 
    where ROWNUM = MOD(MOD (number_RANDOM(),42 -G1_Terr) , ROWNUM) and id_territory NOT IN (select Player_owner from territory where ; 

    G2_Terr := var_cur.number_territories_tot ;  
    end if ; 
if (cont=3) then 


    update territory 
    set Player_owner = var_cur.id_Player 
    where ROWNUM <= 42 - G1_Terr - G2_Terr; 
    --where ROWNUM <= 42 - var_cur.number_territories_tot * 2 ; 
G3_Terr := var_cur.number_territories_tot ; 
end if ; 
end loop; 
end; 

end;` 

number_random代码：

 create or replace FUNCTION "NUMBER_RANDOM" 
     return NUMBER 
     is 
     numbrndm int; 
     begin 

     select dbms_random.value(1,99999999) into numbrndm 
     from dual; 
    return numbrndm; 
    end; 

Answer 1

如果我理解正确的，你想分配给每个玩家随机设置的区域？

如果是这样，请按照随机顺序对领土进行排序并将其分配给玩家循环赛。这是一个MERGE声明，应该这样做。对于任何小的语法错误，我表示歉意：我现在不在Oracle数据库之前。

MERGE INTO territory t 
USING (
WITH players AS (SELECT rownum player#, id_player 
        FROM player 
        WHERE idgame=:idgame), 
players_count AS (SELECT count(*) players_count FROM players) 
-- Get the id_player for each assignment 
SELECT assignedt.id_territory, p.id_player 
FROM 
(
    -- Assign the randomly-ordered territories to players in round-robin format 
    SELECT randt.id_territory, 
    mod(rownum, pc.players_count)+1 player# 
    FROM (
    -- Query the territories in a random order 
    SELECT t.id_territory 
    FROM territory t 
    ORDER BY dbms_random.random) randt, players_count pc 
    -- Optional: 
    -- ... add a where clause to limit rownum <= some number to make 
    -- ... sure each player receives an even number of territories. 
    -- ... You'd need the count of territories to do that. 
) assignedt inner join players p on p.player# = assignedt.player# 
) u 
ON (u.id_territory = t.id_territory) 
WHEN MATCHED THEN UPDATE SET t.id_player = u.id_player; 

Answer 2

我把你的SQL的随机数列中选择每一行。然后你可以直接在SQL中用随机数字来订购你的记录集。

对玩家和地区名单都这样做。

这个例子应该适用于任何数量的领土和玩家。如果领土不平分，那么随机哪个球员会得到一个额外的领土。

我们将按照随机顺序遍历地区列表一次。我们将逐步浏览玩家列表，直到完成，然后重新载入玩家列表。这需要多次发生。

可能需要修复此语法或逻辑。我没有一个Oracle实例来测试它。

CREATE OR REPLACE PROCEDURE giveterritory (pID_GAME PLS_INTEGER) IS 
    CURSOR c_player IS 
     SELECT id_player, dbms_random.value() rnd 
     FROM players 
     WHERE id_game = pID_GAME 
     ORDER BY 2 desc; 
    -- 
    CURSOR c_territory IS 
     SELECT id_territory, dbms_random.value() rnd 
     FROM territories 
     WHERE id_game = pID_GAME 
     FOR UPDATE of id_player 
     ORDER BY 2 desc; 
    -- 
    l_player_row c_player%ROWTYPE; 
    l_territory_row c_territory%ROWTYPE; 
BEGIN 
    OPEN c_player; 
    FETCH c_player into l_player_row; 
    -- 
    FOR r in c_territory 
    LOOP 
     UPDATE territories 
     SET id_player = l_player_row.id_player 
     WHERE CURRENT OF c_territory; 
     -- 
     FETCH c_player INTO l_player_row; 
     IF c_player%NOTFOUND THEN 
      CLOSE c_player; 
      OPEN c_player; 
      FETCH c_player INTO l_player_row; 
     END IF; 
    END LOOP; 
END;