SELECT *,aa.SVR_NAME AS SVR_NEW_NAME FROM T_OLD AS a1
CROSS APPLY (SELECT DISTINCT TOP 1 a2.SVR_NAME FROM T_OLD AS a2 WHERE NOT EXISTS (SELECT 0 FROM T_OLD AS a3 WHERE a3.CUS_ID=a1.CUS_ID AND a3.SVR_NAME=a2.SVR_NAME)) aa
CREATE PROCEDURE [dbo].[Get_T_NEW]
AS
BEGIN
/*用编号代替经理*/
SELECT SVR_NAME,ROW_NUMBER() OVER(ORDER BY MIN(CUS_ID)) AS JL INTO #JL FROM T_OLD GROUP BY SVR_NAME
SELECT A.CUS_ID AS KH, B.JL, ROW_NUMBER() OVER(PARTITION BY B.JL ORDER BY A.CUS_ID) AS PX INTO #T_OLD FROM T_OLD AS A INNER JOIN #JL AS B ON A.SVR_NAME = B.SVR_NAME
/*求平均数,为条件2做准备，随便求下经理数*/
DECLARE @PJS INT,@JLS INT
SELECT @PJS = COUNT(DISTINCT KH) / COUNT(DISTINCT JL), @JLS = COUNT(DISTINCT JL) FROM #T_OLD
/*创建#T_NEW表，结构和#T_OLD一致*/
CREATE TABLE #T_NEW (KH INT, JL INT);
/*将原本大于等于平均数的经理名下的客户分配出去，
这样分配能保证其他经理至少能分配到某经理名下的一名客户
满足了条件3
PS:这个备注到下个备注之间，用游标写应该会更好理解，不过我不会游标^_^*/
WITH T1 AS
(
SELECT
*,
(ROW_NUMBER() OVER(PARTITION BY JL ORDER BY KH) - 1) % (@JLS - 1) + 1 AS JL_KH_ID
FROM
#T_OLD
WHERE
JL IN (SELECT JL FROM #T_OLD GROUP BY JL HAVING COUNT(KH) >= @PJS)
)
INSERT INTO #T_NEW
SELECT KH, JL_KH_ID + CASE WHEN JL_KH_ID >= JL THEN 1 ELSE 0 END AS NEW_JL FROM T1
/*这些客户已经分配出去了，所以删除这些旧的数据，剩下的就是未分配的了*/
DELETE FROM #T_OLD WHERE KH IN (SELECT KH FROM #T_NEW)
/*接下来求出按这个初步分配方案，还有多少位经理缺少客户*/
DECLARE @WMJLS/*未满经理数*/ INT
SELECT @WMJLS = COUNT(DISTINCT JL) FROM #T_OLD
/*循环分配，每一次循环分配掉一个经理名下的客户，直到所有客户分配完毕*/
DECLARE @DFPJL/*待分配经理*/ INT
WHILE @WMJLS <> 0
BEGIN
/*首先拿出一个未分配完的经理*/
SELECT @DFPJL = MIN(JL) FROM #T_OLD
/*然后求出还能分配客户的经理，并对他们按顺序编号，最大编号为@WMJLS，未满经理ID*/
SELECT JL,ROW_NUMBER() OVER(ORDER BY JL) AS WMJLID INTO #WMJL FROM #T_NEW WHERE JL <> @DFPJL GROUP BY JL HAVING @PJS > COUNT(KH)
/*再按顺序依次分配*/
INSERT INTO #T_NEW
SELECT
#T_OLD.KH,#WMJL.JL
FROM
#T_OLD INNER JOIN #WMJL ON (#T_OLD.PX - 1) % @WMJLS + 1 = #WMJL.WMJLID
WHERE
#T_OLD.JL = @DFPJL
/*删除已分配的服务关系*/
DELETE FROM #T_OLD WHERE JL = @DFPJL
/*删除#WMJL，方便下次创建*/
DROP TABLE #WMJL
/*最后再次计算未分配满的经理数*/
SELECT @WMJLS = COUNT(DISTINCT JL) FROM #T_OLD
END
/*最后的最后，把经理ID替换会经理姓名*/
SELECT #T_NEW.KH AS CUS_ID,#JL.SVR_NAME FROM #T_NEW INNER JOIN #JL ON #T_NEW.JL = #JL.JL ORDER BY CUS_ID
DROP TABLE #T_NEW
END
“System.Linq.IQueryable”不包含“Contains”的定义 或者“System.Linq.IEnumberable”不包含“Contains”的定义 例如： from m in TeachingInfo where (from t in TeacherInfo where (from o in ...
一、C++代码 ...string 转 int .............................. char* 转 int #include int atoi(const char *nptr); long atol(const char *nptr); long long atoll(const char *nptr); long long
