学生分班SQL实现

/*
某中学有2个重点班18个普通班
要求:
1.重点班人数占总人数的20%
2.平均分越接近越好,性别比越接近越好
3.尽量不要出现班级同名的情况
*/

--用到的表
CREATE TABLE [dbo].[NBAC_dbo_Scores]
(
	[student] [varchar](50) NULL,
	[subject] [varchar](50) NULL,
	[score] [numeric](18, 1) NULL
) ON [PRIMARY]
go

CREATE TABLE [dbo].[NBAC_dbo_Students]
(
	[stuno] [float] NULL,
	[stuname] [nvarchar](255) NULL,
	[sex] [nvarchar](255) NULL
) ON [PRIMARY]
GO
--数据

--计算普通班和重点班
SELECT a.student,stuname,a.zcj,b.sex ,
CASE when 100.0*ROW_NUMBER() OVER(ORDER BY a.zcj)/COUNT(1) OVER() >=COUNT(1) OVER()/20.0 *2 THEN '重点' ELSE '普通' END 类型 ,CONVERT(int,null) AS 班级
INTO #tmp
FROM 
(
  SELECT student,SUM(score) AS zcj FROM dbo.NBAC_dbo_Scores GROUP BY student
)  
a INNER JOIN  dbo.NBAC_dbo_Students b ON a.student = b.stuno 
 

--故意设置重名数据
UPDATE  #tmp SET stuname='小马' WHERE student='201818039'--赖银兵
UPDATE  #tmp SET stuname='小马' WHERE student='201801016'--姜效文
UPDATE  #tmp SET stuname='小马' WHERE student='201807007'--王涛
UPDATE  #tmp SET stuname='小马' WHERE student='201818033'--刘兴鑫
UPDATE  #tmp SET stuname='小马' WHERE student='201801020'--曾传慧

--创建班级临时表
CREATE TABLE #ban
(
	number INT,
	isFen BIT
)


DECLARE @banj INT 
DECLARE @student VARCHAR(10)

--普通班
SET @banj=0
SET @student=''

INSERT INTO #ban(number,isFen)
SELECT number,0  FROM master..spt_values WHERE type='P' AND number BETWEEN 1 AND 18 
 
WHILE 1=1
BEGIN 
	SELECT TOP 1 @banj=number FROM #ban a  WHERE isFen=0 ORDER BY (SELECT AVG(zcj) FROM #tmp WHERE 班级=a.number) desc
	UPDATE #ban SET isFen=1 WHERE number=@banj
	SELECT TOP 1 @student=a.student FROM #tmp  a WHERE 类型='普通' AND 班级 IS NULL AND NOT EXISTS(SELECT 1 FROM #tmp WHERE 班级=@banj AND stuname=a.stuname) 
	ORDER BY sex,zcj
	 
	IF @student=''
	SELECT TOP 1 @student=a.student FROM #tmp  a WHERE 类型='普通' AND 班级 IS NULL  
	ORDER BY sex,zcj
	
	IF @student=''
		BREAK
	UPDATE #tmp SET 班级=@banj WHERE student=@student
	
	SET @student=''
	
	IF NOT EXISTS(SELECT 1 FROM #ban WHERE isFen=0 ) 
	BEGIN 
		UPDATE #ban SET isFen=0
	END 	
END 

--重点班
SET @banj=0 
SET @student=''

TRUNCATE TABLE #ban
INSERT INTO #ban(number,isFen)
SELECT number,0  FROM master..spt_values WHERE type='P' AND number BETWEEN 1 AND 2 

WHILE 1=1
BEGIN 
	SELECT TOP 1 @banj=number FROM #ban a  WHERE isFen=0 ORDER BY (SELECT AVG(zcj) FROM #tmp WHERE 班级=a.number) desc
	UPDATE #ban SET isFen=1 WHERE number=@banj
	
	SELECT TOP 1 @student=a.student FROM #tmp  a WHERE 类型='重点' AND 班级 IS NULL AND NOT EXISTS(SELECT 1 FROM #tmp WHERE 班级=@banj AND stuname=a.stuname) 
	ORDER BY sex,zcj
	IF @student=''
	SELECT TOP 1 @student=a.student FROM #tmp  a WHERE 类型='重点' AND 班级 IS NULL 
	ORDER BY sex,zcj
	
	IF @student=''
		BREAK
	UPDATE #tmp SET 班级=@banj WHERE student=@student
	
	SET @student=''
	 
	IF NOT EXISTS(SELECT 1 FROM #ban WHERE isFen=0 ) 
	BEGIN
		UPDATE #ban SET isFen=0
	END 
	 
END 

 
--调整普通班,平均分误差设置0.1,最大调整次数10000
DECLARE @banj1 INT
DECLARE @banj2 INT
DECLARE @pjf1 NUMERIC(18,6)
DECLARE @pjf2 NUMERIC(18,6)
DECLARE @stu1 VARCHAR(10)
DECLARE @stu2 VARCHAR(10)
DECLARE @xb VARCHAR(10)
DECLARE @zcj NUMERIC(18,6)
DECLARE @xm1 VARCHAR(10)
DECLARE @xm2 VARCHAR(10)
DECLARE @i INT 
DECLARE @jd NUMERIC(18,6)=0.1


SET @pjf1 =1
SET @pjf2 =0
SET @i = 0

WHILE  ABS(@pjf1-@pjf2)>=@jd AND @i<10000
BEGIN 
	SET @stu1=''
	SET @stu2=''
	SELECT TOP 1 @banj1=班级,@pjf1=AVG(zcj) FROM #tmp WHERE 类型='普通' GROUP BY 班级 ORDER BY 2
	SELECT TOP 1 @banj2=班级,@pjf2=AVG(zcj) FROM #tmp WHERE 类型='普通' GROUP BY 班级 ORDER BY 2 DESC
	
	SELECT @stu1=student,@xb=sex,@zcj=zcj,@xm1=stuname FROM #tmp WHERE 班级=@banj1 AND  类型='普通' ORDER BY  NEWID()
	SELECT @stu2=student,@xm2=stuname FROM #tmp WHERE 班级=@banj2 AND sex=@xb AND zcj>@zcj  AND  类型='普通' ORDER BY NEWID()
 
	IF @stu1<>'' AND @stu2<>'' 
	BEGIN 
		IF NOT EXISTS(SELECT 1 FROM #tmp WHERE 班级=@banj1 AND stuname=@xm2) AND NOT EXISTS(SELECT 1 FROM #tmp WHERE 班级=@banj2 AND stuname=@xm1)
		BEGIN  
			UPDATE #tmp SET 班级=@banj2 WHERE student=@stu1
			UPDATE #tmp SET 班级=@banj1 WHERE student=@stu2
		END 
	END 
	
	
	SELECT TOP 1 @pjf1=AVG(zcj) FROM #tmp WHERE 类型='普通' AND 班级 NOT IN(@pjf1,@pjf2) GROUP BY 班级 ORDER BY 1
	SELECT TOP 1 @pjf2=AVG(zcj) FROM #tmp WHERE 类型='普通' AND 班级 NOT IN(@pjf1,@pjf2) GROUP BY 班级 ORDER BY 1 DESC
 
	SET @i=@i+1
END 

--调整重点班
SET  @pjf1 =1
SET @pjf2 =0
SET @i = 0
WHILE  ABS(@pjf1-@pjf2)>=@jd AND @i<10000
BEGIN 
	SET @stu1=''
	SET @stu2=''
	SELECT TOP 1 @banj1=班级,@pjf1=AVG(zcj) FROM #tmp WHERE 类型='重点' GROUP BY 班级 ORDER BY 2
	SELECT TOP 1 @banj2=班级,@pjf2=AVG(zcj) FROM #tmp WHERE 类型='重点' GROUP BY 班级 ORDER BY 2 DESC
	
	SELECT @stu1=student,@xb=sex,@zcj=zcj,@xm1=stuname FROM #tmp WHERE 班级=@banj1 AND  类型='重点' ORDER BY  NEWID()
	SELECT @stu2=student,@xm2=stuname FROM #tmp WHERE 班级=@banj2 AND sex=@xb AND zcj>@zcj AND  类型='重点' ORDER BY NEWID()
 
	IF @stu1<>'' AND @stu2<>'' 
	BEGIN 
		IF NOT EXISTS(SELECT 1 FROM #tmp WHERE 班级=@banj1 AND stuname=@xm2) AND NOT EXISTS(SELECT 1 FROM #tmp WHERE 班级=@banj2 AND stuname=@xm1)
		BEGIN  
			UPDATE #tmp SET 班级=@banj2 WHERE student=@stu1
			UPDATE #tmp SET 班级=@banj1 WHERE student=@stu2
		END 
	END 
	
	
	SELECT TOP 1 @pjf1=AVG(zcj) FROM #tmp WHERE 类型='重点' AND 班级 NOT IN(@pjf1,@pjf2) GROUP BY 班级 ORDER BY 1
	SELECT TOP 1 @pjf2=AVG(zcj) FROM #tmp WHERE 类型='重点' AND 班级 NOT IN(@pjf1,@pjf2) GROUP BY 班级 ORDER BY 1 DESC
 
	SET @i=@i+1
END 
 
 
--统计结果
SELECT 班级,AVG(zcj) AS 平均分 FROM #tmp WHERE 类型='普通' GROUP BY 班级 ORDER BY 2
SELECT 班级,SUM(IIF(sex='男',1,0)) AS 男,SUM(IIF(sex='男' ,0,1)) AS 女,COUNT(1) AS 总人数 FROM #tmp WHERE 类型='普通' GROUP BY 班级 ORDER BY 班级

SELECT 班级,AVG(zcj) AS 平均分 FROM #tmp WHERE 类型='重点' GROUP BY 班级 ORDER BY 班级
SELECT 班级,SUM(IIF(sex='男',1,0)) AS 男,SUM(IIF(sex='男' ,0,1)) AS 女,COUNT(1) AS 总人数 FROM #tmp WHERE 类型='重点' GROUP BY 班级 ORDER BY 班级

SELECT 班级,stuname,COUNT(1) AS 人数  FROM #tmp  WHERE 类型='重点'   GROUP BY 班级,stuname HAVING COUNT(1)>1
SELECT 班级,stuname,COUNT(1) AS 人数  FROM #tmp  WHERE 类型='普通'   GROUP BY 班级,stuname HAVING COUNT(1)>1


DROP TABLE #tmp 
DROP TABLE #ban