A表：
Aid type cname sname
B表:
Bid sname rank
C表（统计表）：
Cid sname type1,type2,type3,type4
假设：
A表当天新增了1000笔客户数据，其中A.type = 1的100笔，A.type =2的300笔，A.type =3的500笔，A.type =4的100笔
B表里面当天b.rank=2的有20人，b.rank=1的有15人，b.rank=0的有5人，共40人
B表sname是来自于A表sname的
希望创建一个sql的作业，每天0点自动运行sql语句，实现：
1、把当天A表的1000笔客户数据分配出去，B表里面b.rank=2+b.rank=1的有35人参与客户分配，平均每个人可以分到1000/35个，b.rank=0的人不参与分配
2、A.type = 1的数据只能平均分配给b.rank=2的20人，意味着b.rank=2的人每人大约能分配到100/20个客户，剩下的从其它type值里面分配，分配方式均为随机分配
3、分配的方式为把b.sname的值写入a.sname
4、每个b.sname分配到的客户写入C表来作为统计日志
举例如下：
b.sname="john"的当天共分配到了100/20+900/35笔≈31笔数据（也就是A表1000笔数据里面有28笔的A.sname="john"）
其中：数据分配到了A.type = 1的5笔（100/20=5）,A.type = 2的9笔（300/35≈9），A.type = 3的14笔（500/35≈14）,A.type = 4的3笔（100/35≈3）共约31笔
将sname="john"的type值对应的1，2，3，4写入C表对应的type1,type2,type3,type4
给出语句马上给分，谢谢！
------解决方案--------------------
drop table a;
drop table b;
drop table c;
CREATE TABLE a(aid INT PRIMARY KEY,[type] INT,sname NVARCHAR(50))
CREATE TABLE b(bid INT PRIMARY KEY,sname NVARCHAR(50),[rank] INT)
CREATE TABLE c(cid INT IDENTITY(1,1) PRIMARY KEY,[Type] int,sname NVARCHAR(50),objectId int);
GO
INSERT INTO [dbo].[a] ([aid],[type],[sname])
SELECT ((SELECT ISNULL(MAX(ocxa.[aid]),10) FROM a ocxa)+ROW_NUMBER() OVER ( ORDER BY GETDATE())) AS rId
,1,null FROM MASTER.dbo.spt_values sv WHERE sv.[type]='P' AND sv.number<100;
INSERT INTO [dbo].[a] ([aid],[type],[sname])
SELECT ((SELECT ISNULL(MAX(ocxa.[aid]),10) FROM a ocxa)+ROW_NUMBER() OVER ( ORDER BY GETDATE())) AS rId
,2,null FROM MASTER.dbo.spt_values sv WHERE sv.[type]='P' AND sv.number<300;
INSERT INTO [dbo].[a] ([aid],[type],[sname])
SELECT ((SELECT ISNULL(MAX(ocxa.[aid]),10) FROM a ocxa)+ROW_NUMBER() OVER ( ORDER BY GETDATE())) AS rId
,3,null FROM MASTER.dbo.spt_values sv WHERE sv.[type]='P' AND sv.number<500;
INSERT INTO [dbo].[a] ([aid],[type],[sname])
SELECT ((SELECT ISNULL(MAX(ocxa.[aid]),0) FROM a ocxa)+ROW_NUMBER() OVER ( ORDER BY GETDATE())) AS rId
,4,null FROM MASTER.dbo.spt_values sv WHERE sv.[type]='P' AND sv.number<100;
INSERT INTO [dbo].[b] ([bid],[sname],[rank])
SELECT ((SELECT ISNULL(MAX(ocxa.[bid]),0) FROM b ocxa)+ROW_NUMBER() OVER ( ORDER BY GETDATE())) AS rId
,'2_'+ltrim(NEWID()),2 FROM MASTER.dbo.spt_values sv WHERE sv.[type]='P' AND sv.number<20;
INSERT INTO [dbo].[b] ([bid],[sname],[rank])
SELECT ((SELECT ISNULL(MAX(ocxa.[bid]),0) FROM b ocxa)+ROW_NUMBER() OVER ( ORDER BY GETDATE())) AS rId
,'1_'+ltrim(NEWID()),1 FROM MASTER.dbo.spt_values sv WHERE sv.[type]='P' AND sv.number<15;
INSERT INTO [dbo].[b] ([bid],[sname],[rank])
SELECT ((SELECT ISNULL(MAX(ocxa.[bid]),0) FROM b ocxa)+ROW_NUMBER() OVER ( ORDER BY GETDATE())) AS rId
,'0_'+ltrim(NEWID()),0 FROM MASTER.dbo.spt_values sv WHERE sv.[type]='P' AND sv.number<5;
PRINT '---------------'
IF OBJECT_ID('tempdb..#b') IS NOT NULL
BEGIN
DROP TABLE #b
END
SELECT * INTO #b FROM b WHERE b.[rank]=2;
DECLARE @acount INT=0;
DECLARE @bcount INT=0;
SET @acount=(SELECT COUNT(1) FROM a WHERE a.type=1 AND a.sname IS NULL )
SET @bcount=(SELECT COUNT(1) FROM b WHERE b.[rank]=2)
--SELECT ((@acount+@bcount-1)/@bcount);--总页数
/*平均分配*/
;with tx as(
SELECT a.*,b.bid,b.sname AS snameNew FROM (
SELECT a.*,ROW_NUMBER() OVER ( ORDER BY a.aid) Rn FROM a
where a.type=1 AND a.sname IS NULL
) a
INNER JOIN (
SELECT b.*,ROW_NUMBER() OVER ( ORDER BY r.number ,b.bid) Rn
FROM #b b
INNER JOIN (SELECT * FROM MASTER.dbo.spt_values sv
WHERE sv.[type]='P'
AND sv.number<((@acount+@bcount-1)/@bcount))
r ON 1=1
) b ON a.Rn=b.Rn
)
UPDATE tx SET sname=snameNew
OUTPUT INSERTED.type,INSERTED.sname,INSERTED.aid INTO c;--select * from tx;