sql - SQL查询让 child 有相等数量的玩具

试图找出SQL查询来平衡孩子之间的玩具。
在Children表中，列是：

ID INT, <br>
FNAME NCHAR(50), <br>
LNAME NCHAR(50), <br>
NumberofToys INT

那么在Toys表中我们有：

ID INT, <BR>
ToyName NCHAR(50) <BR>
ChildrenID INT -->This is a FK to the Children table

所以这里的关系是一对多（一个孩子可以有0对多的玩具）。
在孩子们中间平衡玩具意味着：
如果有n个孩子和m个玩具，那么每个孩子应该有（m/n）个玩具
如果有n个孩子和n+1个玩具，那么每个孩子都有（m/n）个玩具，其中一个孩子在桌子上有n+1个玩具。
如果有n个孩子和n-1个玩具，那么每个孩子都有（m/n）个玩具，其中一个孩子在桌子上有n-1个玩具。
如果已经达到平衡，并且要从桌子上取下一个孩子，那么他们的玩具应该一次平均分配一个，直到他们的玩具份额完全分配。
如果已经平衡，并且孩子的一个玩具被从玩具桌上取下，那么桌子上剩余的玩具应该被均匀地重新分配
样本集（预平衡）：

Children
ID    FNAME    LNAME    NumberofToys

1      Bob      Jones     3
2      Jenny    James     5


Toys
ID    ToyName    ChildrenID
1      Bear       1
2      Train      2
3      Truck      2
4      Car        2

解决方案集：

Children
ID    FNAME    LNAME    NumberofToys

1      Bob      Jones      2
2      Jenny     James     2


Toys
ID    ToyName    ChildrenID
1      Bear       1
2      Train      1
3      Truck      2
4      Car        2

我好像找不到正确的算法。一旦玩具被添加到玩具表中，fk应该是第一个玩具数量最少的儿童id。我试过的是：

DECLARE @ToyCount INT,
@ID INT
--This should take care of the balancing problem.
--Since Toys get added one at a time, just add to first Child
----with minimum amount of customers
SET @ToyCount = (SELECT MIN(NumberofToys) FROM Children)

--Store top id that has least amount of toys
    SET @ID = (SELECT TOP 1 ID
    FROM Children
    WHERE @ToyCount=NumberofToys)
--This part is if a Child needs to be added to the table, NumberofToys would be originally 0
    INSERT INTO Children (FNAME, LNAME, NumberofToys)
    VALUES(@fname, @lname, 0);


--Increasing the number of toys for the ID we SET above
UPDATE Children
SET NumberofToys = NumberofToys + 1
WHERE ID=@ID

如果用户手动“不平衡”儿童玩具的数量，则需要进行平衡上面的章节做了添加修复任何新玩具，但不是预先存在的。有人能帮我吗？我对现有玩具的尝试如下：

DECLARE @ToyCount INT, @ToyCountMAX,
@ID INT, @IDMAX INT, @IDTarget INT

SET @ToyCount = (SELECT MIN(NumberofToys) FROM Children)
SET @ToyCountMAX = (SELECT MAX(NumberofToys) FROM Children)


--Store top id that has least amount of toys
    SET @ID = (SELECT TOP 1 ID
    FROM Children
    WHERE @ToyCount=NumberofToys)
--Store top id that has most amount of toys
SET @IDMAX = (SELECT TOP 1 ID FROM Children WHERE @ToyCountMAX=NumberofToys)

--Take top id that has most amount of toys and set it to an ID that has least amount (I believe my problem is here. Can't get the syntax to set the top ChildrenID to the SELECT...on the right side of the = sign
UPDATE Toys
SET ChildrenID = (SELECT TOP 1 ChildrenID FROM Children WHERE NumberofToys=MIN(NumberofToys)
WHERE ChildrenID=@IDMAX
    --Increasing the number of toys for the ID we SET above
    UPDATE Children
    SET NumberofToys = NumberofToys + 1
    WHERE ID=@ID

--decreasing number of toys for the max ID
UPDATE Children
SET NumberofToys = NumberofToys - 1
WHERE ID=@IDMAX

最佳答案

尝试下面的存储过程，它应该在任何阶段在Toys之间保持平衡。
注意：从Children表中删除子项时，需要维护外键关系。因此，
把那孩子的玩具分配给桌子上的其他孩子
然后从表中删除子项Children
调用存储过程以重新平衡
存储过程

CREATE PROCEDURE BalanceToys
AS
BEGIN

    -- While there is imbalance between children
    -- i.e. MAX toys owned minus MIN toys owned is greater than 1
    WHILE((SELECT MAX(cnt)-MIN(cnt) FROM (
        SELECT c.ID, SUM(CASE WHEN t.ID IS NOT NULL THEN 1 ELSE 0 END) Cnt
        FROM Children c
            LEFT JOIN Toys t ON c.ID = t.ChildrenID
        GROUP BY c.ID) v) > 1)
    BEGIN

        -- We rebalance 1 toy at a time
        -- the child with most toys will give 1 toy to the child with least toys
        UPDATE TOP (1) Toys
        SET ChildrenID = (SELECT TOP 1 c.ID
            FROM Children c
                LEFT JOIN Toys t ON c.ID = t.ChildrenID
            GROUP BY c.ID
            ORDER BY SUM(CASE WHEN t.ID IS NOT NULL THEN 1 ELSE 0 END))
        WHERE ChildrenID = (SELECT TOP 1 c.ID
            FROM Children c
                LEFT JOIN Toys t ON c.ID = t.ChildrenID
            GROUP BY c.ID
            ORDER BY SUM(CASE WHEN t.ID IS NOT NULL THEN 1 ELSE 0 END) DESC)

       -- Loop until all balanced
    END

    -- Update NumberofToys
    UPDATE Children
    SET NumberofToys = (SELECT COUNT(*) FROM Toys WHERE ChildrenID = Children.ID)

END
GO