如何在MS SQL中将guid字段从1行交换到另一行？ [英] How do I swap guid fields from 1 row to another in MS SQL ?

问题描述

好的，我对SQL很新，所以我可能会离开这里，但这就是我想做的事情。我有tblFixtures，其中包含Guid的玩家，这些玩家在Pool Knockout中被吸引到对方玩耍。 tblFixtures有uniqueidentifier列player1和player2。我已经添加了'Byes'，因为像这样的淘汰赛所需的总玩家数是8 ..所以例如，如果在tblFixtures中只有6名玩家，它会添加2个空白玩家并且Guid为'00000000-0000-0000-0000- 000000000000' 。因此，2名玩家将获得下一轮（A'Bee'）的免费通行证，但我需要重新安排桌子，以防BYE最终对抗这样的BYE。





玩家1玩家2

{72462373-6B64-4427-9F9F-9A872261A333} v {9C90B324-78CA-4C7B-BC79-15537F3935B2}

{00000000-0000-0000-0000-000000000000} v {00000000-0000-0000-0000-000000000000}

{3E828BB1-AEC2-4543-BD26-E12AD886D2E8} v {72462373-6B64-4427-9F9F-9A872261ABC2}

{4F9C3964-2672-4413-8A01-2ED09CA7A711} v {AFEB20EB-5CCB-4302-A999-CC772F8ABACE}

我需要把空Guid的ANYWHERE对抗这样的球员Guid ..





球员1球员2

{72462373-6B64-4427-9F9F-9A872261A333} v {9C90B324-78CA-4C7B-BC79-15537F3935B2}

{3E828BB1-AEC2-4543-BD26-E12AD886D2E8} v {00000000 0000-0000-0000-000000000000}

{00000000-0000-00 00-0000-000000000000} v {72462373-6B64-4427-9F9F-9A872261ABC2}

{4F9C3964-2672-4413-8A01-2ED09CA7A711} v {AFEB20EB-5CCB-4302-A999-CC772F8ABACE}



下面的代码需要OVER命令和我认为的一些调整，但这是我能得到的。我要做的是检查有多少玩家（包括BYES），所以在这种情况下有8个，并存储在@matches中。现在我希望它一次检查一行，使用一个等于@matches中存储量的循环。这个检查查找空白0的Guid播放空白0的Guid。如果找到它，那么它将替换此行中的Player1与下一个没有空白的玩家1 Guid它找到并将其归零。



我只是不知道了解OVER还是不确定其余的代码是否真的有效或者有没有人知道更简单的方法呢？



任何帮助表示感谢。这是我到目前为止所拥有的......



DECLARE @matchess int

SELECT @matches =来自tblFixtures的COUNT（accountID）*



DECLARE @cnt INT = 0



我的尝试：

Ok, I'm fairly new to SQL so I'm probably way off here but here's what I want to do. I have tblFixtures which holds Guid's of players who have been drawn to play each other in a Pool Knockout. tblFixtures has uniqueidentifier columns player1 and player2. I have already added 'Byes' because total players needed for a knockout like this is 8.. so for example, if a there's only 6 players in tblFixtures, it adds 2 blank players with Guid of '00000000-0000-0000-0000-000000000000'. Therefore 2 players will get a 'Free pass to the next round (A 'Bye') but I need to re-arrange the table in case a BYE ends up drawn against a BYE like this.


Player 1 Player 2
{72462373-6B64-4427-9F9F-9A872261A333} v {9C90B324-78CA-4C7B-BC79-15537F3935B2}
{00000000-0000-0000-0000-000000000000} v {00000000-0000-0000-0000-000000000000}
{3E828BB1-AEC2-4543-BD26-E12AD886D2E8} v {72462373-6B64-4427-9F9F-9A872261ABC2}
{4F9C3964-2672-4413-8A01-2ED09CA7A711} v {AFEB20EB-5CCB-4302-A999-CC772F8ABACE}
I need it to put the empty Guid's ANYWHERE against a players Guid like this..


Player 1 Player 2
{72462373-6B64-4427-9F9F-9A872261A333} v {9C90B324-78CA-4C7B-BC79-15537F3935B2}
{3E828BB1-AEC2-4543-BD26-E12AD886D2E8} v {00000000-0000-0000-0000-000000000000}
{00000000-0000-0000-0000-000000000000} v {72462373-6B64-4427-9F9F-9A872261ABC2}
{4F9C3964-2672-4413-8A01-2ED09CA7A711} v {AFEB20EB-5CCB-4302-A999-CC772F8ABACE}

The code below requires OVER command and a few tweaks I think, but this is as far as I could get. What I am trying to do is check how many players (including BYES) there are, so in this case there's 8 and that's stored in @matches. Now I want it to check a row at a time, using a loop equal to the amount stored in @matches.. This check looks for a blank 0's Guid playing a blank 0's Guid. If it is found then it will replace Player1 in this row with the next none blank player1 Guid it finds and zero that one out instead.

I just don't know understand OVER yet and unsure the rest of the code will actually work OR does anyone know an easier way to do this?

Any help appreciated. Here's what I have so far..

DECLARE @matchess int
SELECT @matches = COUNT(accountID) from tblFixtures)*

DECLARE @cnt INT = 0

What I have tried:

SELECT player1, ROW_NUMBER() OVER (ORDER BY player1) AS RowNumber
FROM   tblFixtures


DECLARE @Players int
SELECT @Players = COUNT(accountID) from tblEntrants WHERE paid='y'

DECLARE @cnt INT = 0

WHILE @cnt < @Players
    BEGIN

    If (SELECT Player1 WHERE ROW_NUMBER()  OVER (ORDER BY Player1) FROM tblFixtures)=@cnt = '00000000-0000-0000-0000-000000000000' AND SELECT(player1 WHERE ROW_NUMBER()  OVER (ORDER BY Player1 FROM tblFixtures)= @cnt) ='00000000-0000-0000-0000-000000000000' THEN
        BEGIN
            UPDATE tblFixtures SET Player1 = SELECT(TOP(1) player1  WHERE player1 <> '00000000-0000-0000-0000-000000000000' AND ROW_NUMBER()  OVER (ORDER BY Player1) FROM tblFixtures>@cnt) WHERE ROW_NUMBERr()  OVER (ORDER BY Player1) FROM tblFixtures=@cnt
            UPDATE tblFixtures SET Player1 = '0000-0000-0000-0000' WHERE SELECT(Top(1) player1) <> '00000000-0000-0000-0000-000000000000' AND ROW_NUMBER() FROM tblFixtures>@cnt 
        END
    SET @cnt = @cnt + 1
END

推荐答案

这样的事情应该有效：

Something like this should work:

-- Count the number of real players:
DECLARE @EntrantCount int;

SELECT
    @EntrantCount = Count(1)
FROM
    tblEntrants
WHERE
    paid = 'y'
;


-- Find the next highest power of 2 - the total number of players required:
DECLARE @PlayerCount int = POWER(2, CEILING(LOG(@EntrantCount, 2)));


-- Generate a random order for all entrants and byes:
DECLARE @AllPlayers TABLE 
(
    RN int NOT NULL Primary Key, 
    ID uniqueidentifier NULL
);

WITH ctePlayers As
(
    SELECT
        ID
    FROM
        tblEntrants
    WHERE
        paid = 'y'

    UNION ALL

    SELECT TOP (@PlayerCount - @EntrantCount)
        Null
    FROM
        sys.objects
),
cteOrderedPlayers As
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY NewID()) As RN,
        ID
    FROM
        ctePlayers
)
INSERT INTO @AllPlayers
(
    RN,
    ID
)
SELECT
    RN,
    ID
FROM
    cteOrderedPlayers
;


-- Build the random fixtures:
DECLARE @Fixtures TABLE 
(
    RN int NOT NULL IDENTITY(1, 1) Primary Key, 
    Player1 uniqueidentifier NULL, 
    Player2 uniqueidentifier NULL
);

WITH cteFixtures As
(
    SELECT
        P1.ID As Player1,
        P2.ID As Player2
    FROM
        @AllPlayers As P1
        LEFT JOIN @AllPlayers As P2
        ON P2.RN = P1.RN + 1
    WHERE
        -- Only take the odd-numbered rows for player 1:
        (P1.RN & 1) = 1
)
INSERT INTO @Fixtures
(
    Player1,
    Player2
)
SELECT
    Player1,
    Player2
FROM
    cteFixtures
;


-- Resolve "bye-bye" rows:
WHILE Exists(SELECT 1 FROM @Fixtures WHERE Player1 Is Null And Player2 Is Null)
BEGIN
    DECLARE @R1 int, @R2 int, @ID uniqueidentifier;

    -- Find the first row without a player:
    SELECT TOP 1 
        @R1 = RN 
    FROM 
        @Fixtures 
    WHERE 
        Player1 Is Null 
    And 
        Player2 Is Null
    ;

    -- Find the first row with a player on both sides:
    SELECT TOP 1
        @R2 = RN,
        @ID = Player1
    FROM
        @Fixtures
    WHERE
        Player1 Is Not Null
    And
        Player2 Is Not Null
    ;

    IF @@ROWCOUNT = 0
    BEGIN
        -- Just in case, to avoid an infinite loop, although I'm 99.9% sure this will never happen:
        RAISERROR('No way to resolve - should never happen.', 16, 1);
    END;

    -- Swap player 1:
    UPDATE
        @Fixtures
    SET
        Player1 = CASE RN
            WHEN @R1 THEN @ID
            ELSE Null
        END
    WHERE
        RN In (@R1, @R2)
    ;
END;


-- Finished:
INSERT INTO tblFixtures
(
    Player1,
    Player2
)
SELECT
    IsNull(Player1, '00000000-0000-0000-0000-000000000000'),
    IsNull(Player2, '00000000-0000-0000-0000-000000000000')
FROM
    @Fixtures
;

这篇关于如何在MS SQL中将guid字段从1行交换到另一行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！