有孩子的SQL查询有相同数量的玩具

Question

试图找出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到多个玩具)。

在儿童中平衡玩具意味着以下方面：

1. 如果有n个孩子和m个玩具，那么每个孩子都应该有(m/n)玩具
2. 如果有n个孩子和n+1的玩具数量，那么每个孩子都会有(m/n)玩具，桌子上有一个孩子有n+1玩具。
3. 如果有n数量的儿童和n-1数量的玩具，那么每个儿童将有(m/n)玩具与一个孩子在桌子上有n-1玩具。
4. 如果已经平衡，一个孩子被从桌子上移走，那么他们的玩具应该一次一个均匀地分配，直到他们的玩具完全分配为止。
5. 如果已经平衡好了，并且把孩子的一个玩具从玩具桌上拿走了，那么剩下的玩具就应该被均匀地重新分配了。

样本集(预平衡)：

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

Answer 1

尝试折叠存储过程，它应该平衡Toys之间的Children在任何阶段。

注意:当从Children表中删除子表时，需要维护外键关系。因此，

1. 把那孩子的玩具分配给桌子上的任何其他孩子
2. 然后从表Children中删除子表
3. 调用存储过程以重新平衡

存储过程

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