在SQL服务器中的分配

在SQL服务器中的分配

问题描述:

我需要一个SQL服务器查询的帮助:在SQL服务器中的分配

我有一个分配给它们的订单ID和错误的表。

enter image description here

我需要分配这些订单给团队成员以平等的方式。

enter image description here

有类似以下

enter image description here

enter image description here

我已经能够使用由错误划分的NTILE结果,但是这将拆分的订单团队成员,但不平等。

预先感谢您。

+0

使用NTILE(countOfTeamMembers) –

+0

是否有你正在寻找做任何特别的方式?例如,它可以用多个离散的语句来完成。你在找什么? – STLDeveloper

+0

太多的示例数据... – jarlh

为什么你不应该仅仅因为他们进来分发错误。

; with octe as 
(
select 
    [order #], 
    error, 
    row_number() over(order by error asc) r 
from tblorders 
), 
tcte as 
(
select 
    teammember, 
    row_number() over(order by teammember asc) r 
from tblTeam 
), 
distribution as 
(
select 
    teammember, 
    [order #], 
    error 
from 
octe o join tcte t 
on o.r%(select count(1) from tcte)+1=t.r 
) 

select * from distribution; 

你可以试试NTILE功能。

CREATE TABLE [dbo].[orders](
    [OrderID] [int] IDENTITY(1,1) NOT NULL, 
    [ErrorName] [varchar](50) NULL 
) ON [PRIMARY] 
GO 
/****** Object: Table [dbo].[TeamMembers] Script Date: 10/11/2017 12:53:51 AM ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
CREATE TABLE [dbo].[teamMembers](
    [Memberid] [int] IDENTITY(1,1) NOT NULL, 
    [MemberName] [varchar](50) NULL 
) ON [PRIMARY] 
GO 
SET IDENTITY_INSERT [dbo].[Orders] ON 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (1, N'a') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (2, N'a') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (3, N'a') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (4, N'a') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (5, N'a') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (6, N'b') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (7, N'b') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (8, N'b') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (9, N'b') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (10, N'b') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (11, N'c') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (12, N'c') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (13, N'c') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (14, N'c') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (15, N'c') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (16, N'd') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (17, N'd') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (18, N'd') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (19, N'd') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (20, N'd') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (21, N'e') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (22, N'e') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (23, N'e') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (24, N'e') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (25, N'e') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (26, N'e') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (27, N'f') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (28, N'f') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (29, N'g') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (30, N'g') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (31, N'g') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (32, N'h') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (33, N'i') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (34, N'i') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (35, N'i') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (36, N'i') 
GO 
INSERT [dbo].[Orders] ([OrderID], [ErrorName]) VALUES (37, N'i') 
GO 
SET IDENTITY_INSERT [dbo].[Orders] OFF 
GO 
SET IDENTITY_INSERT [dbo].[TeamMembers] ON 
GO 
INSERT [dbo].[TeamMembers] ([Memberid], [MemberName]) VALUES (1, N'M1') 
GO 
INSERT [dbo].[TeamMembers] ([Memberid], [MemberName]) VALUES (2, N'M2') 
GO 
INSERT [dbo].[TeamMembers] ([Memberid], [MemberName]) VALUES (3, N'M3') 
GO 
INSERT [dbo].[TeamMembers] ([Memberid], [MemberName]) VALUES (4, N'M4') 
GO 
INSERT [dbo].[TeamMembers] ([Memberid], [MemberName]) VALUES (5, N'M5') 
GO 
INSERT [dbo].[TeamMembers] ([Memberid], [MemberName]) VALUES (6, N'M6') 
GO 
SET IDENTITY_INSERT [dbo].[TeamMembers] OFF 
GO 
;WITH cte AS (
SELECT NTILE((SELECT COUNT(*) FROM TeamMembers)) OVER(ORDER BY orderid) as GroupNum, * 
FROM Orders 
) 

SELECT MemberName, cte.OrderID, cte.ErrorName 
FROM cte JOIN dbo.teamMembers ON cte.GroupNum = Memberid 

这为从1开始的每个订单分配一个数字并且与成员相同。然后使用加入成员编号的成员数量的顺序编号的mod来尽可能均等地分配它们。

编辑:如果您通过NEWID()而不是OrderId和MemberId进行排序,那么排序会更随机,但仍然是偶数,并且甚至在没有偶数时,谁得到的变化最大。

DECLARE @orders table (OrderId int NOT NULL PRIMARY KEY CLUSTERED, Error char(1) NOT NULL); 
INSERT INTO @orders (OrderId, Error) 
VALUES 
    (1 , 'a'), 
    (2 , 'a'), 
    (3 , 'b'), 
    (4 , 'e'), 
    (5 , 'e'), 
    (6 , 'a'), 
    (7 , 'b'), 
    (8 , 'e'), 
    (9 , 'd'), 
    (10 , 'd'), 
    (11 , 'a'), 
    (12 , 'e'), 
    (13 , 'e'), 
    (14 , 'c'), 
    (15 , 'i'), 
    (16 , 'b'), 
    (17 , 'f'), 
    (18 , 'g'), 
    (19 , 'c'), 
    (20 , 'b'), 
    (21 , 'f'), 
    (22 , 'g'), 
    (23 , 'c'), 
    (24 , 'i'), 
    (25 , 'g'), 
    (26 , 'h'), 
    (27 , 'i'), 
    (28 , 'h'), 
    (29 , 'd'), 
    (30 , 'c'), 
    (31 , 'i') 
; 

DECLARE @members table (MemberId int NOT NULL PRIMARY KEY CLUSTERED, MemberName varchar(50) NOT NULL); 
INSERT INTO @members (MemberId, MemberName) 
VALUES 
    (1, 'John Smith'), 
    (2, 'Ted Rogers'), 
    (3, 'Amy Williams'), 
    (4, 'Natasha James'), 
    (5, 'Ahmed Benali'), 
    (6, 'Darshit Khatri') 
; 

DECLARE @memberCount int = ( 
    SELECT COUNT(*) 
    FROM @members 
    ); 

WITH 
Orders AS 
(
    SELECT OrderId, Error, Number = ROW_NUMBER() OVER (ORDER BY OrderId) 
    FROM @orders 
), 
Members AS 
(
    SELECT MemberId, MemberName, Number = ROW_NUMBER() OVER (ORDER BY MemberId) - 1 
    FROM @members 
) 

SELECT M.MemberName, O.Error 
FROM Orders O 
JOIN Members M ON O.Number % @memberCount = M.Number 
; 

SELECT o.Order#, 
     o.Error, 
     s.StaffName 
FROM (
      SELECT NTILE((SELECT COUNT(*) FROM Staff)) OVER(ORDER BY Order#) as n, 
        * 
      FROM OrderError 
     ) o 
     JOIN (
       SELECT StaffName, 
         ROW_NUMBER() OVER (ORDER BY StaffName) as MemberID 
       FROM Staff 
      ) s 
      ON o.n = s.MemberID