MS-Access:将记录分配给4个人，每个人的记录数相同

Question

任务是假设我在cutomer表中有4000 (或n)个客户ID，我需要将它们分配给4个人，以便每个人都能处理1000 (或N/4)个客户ID，我尝试了分区，但它给出的结果非常错误，每条记录中有5：。

SELECT CUSTOMER_ID, Partition (CUSTOMER_ID, 1, 4, (SELECT ROUND(Sum(B.C)/4,0) AS   
Employee_ID FROM (SELECT CUSTOMER_ID, Count(CUSTOMER_ID) AS C FROM CUSTOMER GROUP BY 
CUSTOMER_NAME) AS B)/4)
FROM CUSTOMER
GROUP BY CUSTOMER_ID;

Answer 1

这里有一种方法可以做到。对于测试数据

CUSTOMER_ID
-----------
          1
          2
          4
          7
          8
         11
         13
         14
         15
...

查询

SELECT c1.CUSTOMER_ID, COUNT(*) AS RankIndex
FROM CUSTOMER c1 INNER JOIN CUSTOMER c2 ON c1.CUSTOMER_ID >= c2.CUSTOMER_ID
GROUP BY c1.CUSTOMER_ID

将产生

CUSTOMER_ID  RankIndex
-----------  ---------
          1          1  
          2          2
          4          3
          7          4
          8          5
         11          6
         13          7
         14          8
         15          9
...

因此，如果我们在COUNT(*)上做一些模4运算，并将其命名为Employee_ID，如下所示

SELECT c1.CUSTOMER_ID, ((COUNT(*) - 1) Mod 4) AS Employee_ID
FROM CUSTOMER c1 INNER JOIN CUSTOMER c2 ON c1.CUSTOMER_ID >= c2.CUSTOMER_ID
GROUP BY c1.CUSTOMER_ID

我们会得到

CUSTOMER_ID  Employee_ID
-----------  -----------
          1            0  
          2            1
          4            2
          7            3
          8            0
         11            1
         13            2
         14            3
         15            0
...