排名 存储过程 实例

 ●这个实例是我在做项目时候遇到的一个排名的存储过程，根据把得分，分别在不同的月份区间在全国和大区内排名

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE   PROCEDURE [dbo].[proc_mot_sort]
	(@startYear int
	, @startMonth int 
	, @endYear int 
	, @endMonth int
	, @sQuayear int
	, @sQuamonth int
	, @eQuayear int
	, @eQuamonth int 
	) 

AS
BEGIN
	--SET NOCOUNT ON; -- 不返回计数
	DECLARE @temp TABLE --定义临时表
		( stat_year INT
		, stat_month INT
		, larea_pk INT
		, sarea_pk INT
		, dealer_pk BIGINT
		, score DECIMAL(19,14)
		, sort int identity) 
    -- 查询经销商在全国范围内要更新排名的得分
	IF @startYear = @endYear --售后 起始年份与终止年份相同
	BEGIN
		INSERT INTO @temp (stat_year,stat_month,larea_pk,sarea_pk,dealer_pk,score)
		SELECT a.stat_year,a.stat_month,a.larea_pk,a.sarea_pk,a.dealer_pk,a.score
		FROM VMCSS.t_after_mot_statistics a
		JOIN VMCSS.t_dealers_zh c ON a.stat_year = c.stat_year AND a.stat_month = c.stat_month 
		AND a.larea_pk = c.l_area_pk AND a.sarea_pk = c.s_area_pk AND a.dealer_pk = c.pk_id and c.isorder<>0
		WHERE a.dealer_pk <> -1 AND  ((a.stat_year = @startYear AND (a.stat_month >= @startMonth and a.stat_month <= @endMonth) OR 
		(a.stat_year = @sQuayear AND (a.stat_month >= @sQuamonth and a.stat_month <= @eQuamonth))))
		ORDER BY a.stat_year,a.stat_month,a.score DESC
	END
	ELSE --售后 起始年份与终止年份不同
	BEGIN
		INSERT INTO @temp (stat_year,stat_month,larea_pk,sarea_pk,dealer_pk,score)
		SELECT a.stat_year,a.stat_month,a.larea_pk,a.sarea_pk,a.dealer_pk,a.score
		FROM VMCSS.t_after_mot_statistics a
		JOIN VMCSS.t_dealers_zh c ON a.stat_year = c.stat_year AND a.stat_month = c.stat_month 
		AND a.larea_pk = c.l_area_pk AND a.sarea_pk = c.s_area_pk AND a.dealer_pk = c.pk_id and c.isorder<>0
		WHERE a.dealer_pk <> -1  AND ((a.stat_year = @startYear AND (a.stat_month >= @startMonth and a.stat_month <= 12))
		OR (a.stat_year = @endYear AND (a.stat_month >= 1 and a.stat_month <= @endMonth))
		OR (a.stat_year > @startYear AND a.stat_year < @endYear)
		OR (a.stat_year = @sQuayear AND (a.stat_month >= @sQuamonth and a.stat_month <= @eQuamonth))) 
		ORDER BY a.stat_year,a.stat_month,a.score DESC
	END

--整理@temp表中的排名
BEGIN
	DECLARE @cur_year INT
	DECLARE @cur_month INT
	DECLARE @cur_larea INT
	DECLARE @cur_sarea INT
	DECLARE @cur_dealer BIGINT
	DECLARE @cur_score DECIMAL(19,14)
	DECLARE @cur_sort INT

	DECLARE @old_year INT
	DECLARE @old_month INT
	DECLARE @old_larea INT
	DECLARE @old_sarea INT
	DECLARE @old_dealer BIGINT
	DECLARE @old_score DECIMAL(19,14)
	DECLARE @old_sort INT
	DECLARE @delcount INT
	SET @delcount = 1
	DECLARE @cnt INT
	SET @cnt = (SELECT count(1) FROM @temp)

	WHILE @cnt > 0
	BEGIN
		SELECT TOP 1 @cur_year = stat_year
			, @cur_month = stat_month
			, @cur_larea = larea_pk
			, @cur_sarea = sarea_pk
			, @cur_dealer = dealer_pk
			, @cur_score = score
			, @cur_sort = sort
		FROM @temp ORDER BY sort

		IF @cur_year<> @old_year OR @cur_month <> @old_month  -- 公共的条件
		BEGIN
			SET @delcount = @cur_sort
		END
		
		IF @old_score = @cur_score --如果得分和上一名次的相同，则名次也是一样的
		BEGIN
			UPDATE VMCSS.t_after_mot_statistics SET country_sort = @old_sort WHERE stat_year = @cur_year AND stat_month = @cur_month
			AND larea_pk = @cur_larea AND sarea_pk = @cur_sarea AND dealer_pk = @cur_dealer 
		
		END
		ELSE 
		BEGIN
			UPDATE VMCSS.t_after_mot_statistics SET country_sort = @cur_sort - @delcount + 1 WHERE stat_year = @cur_year AND stat_month = @cur_month
			AND larea_pk = @cur_larea AND sarea_pk = @cur_sarea AND dealer_pk = @cur_dealer 

			SET @old_sort = @cur_sort - @delcount + 1	

		END
		SET @old_year = @cur_year
		SET @old_month = @cur_month
		SET @old_larea = @cur_larea
		SET @old_sarea = @cur_sarea
		SET @old_dealer = @cur_dealer
		SET @old_score = @cur_score
		
		DELETE FROM @temp WHERE sort = @cur_sort --删除这一条数据
		SET @cnt = @cnt - 1
	END
END

	-- 查询经销商在大区范围内要更新排名的得分
	IF @startYear = @endYear --售后 起始年份与终止年份相同
	BEGIN
		INSERT INTO @temp (stat_year,stat_month,larea_pk,sarea_pk,dealer_pk,score)
		SELECT a.stat_year,a.stat_month,a.larea_pk,a.sarea_pk,a.dealer_pk,a.score
		FROM VMCSS.t_after_mot_statistics a
		JOIN VMCSS.t_dealers_zh c ON a.stat_year = c.stat_year AND a.stat_month = c.stat_month 
		AND a.larea_pk = c.l_area_pk AND a.sarea_pk = c.s_area_pk AND a.dealer_pk = c.pk_id and c.isorder<>0
		WHERE a.dealer_pk <> -1 AND  ((a.stat_year = @startYear AND (a.stat_month >= @startMonth and a.stat_month <= @endMonth) OR 
		(a.stat_year = @sQuayear AND (a.stat_month >= @sQuamonth and a.stat_month <= @eQuamonth))))		
		ORDER BY a.stat_year,a.stat_month,a.larea_pk,a.score DESC
	END
	ELSE --售后 起始年份与终止年份不同
	BEGIN
		INSERT INTO @temp (stat_year,stat_month,larea_pk,sarea_pk,dealer_pk,score)
		SELECT a.stat_year,a.stat_month,a.larea_pk,a.sarea_pk,a.dealer_pk,a.score
		FROM VMCSS.t_after_mot_statistics a
		JOIN VMCSS.t_dealers_zh c ON a.stat_year = c.stat_year AND a.stat_month = c.stat_month 
		AND a.larea_pk = c.l_area_pk AND a.sarea_pk = c.s_area_pk AND a.dealer_pk = c.pk_id and c.isorder<>0
		WHERE a.dealer_pk <> -1 AND ((a.stat_year = @startYear AND (a.stat_month >= @startMonth and a.stat_month <= 12))
		OR (a.stat_year = @endYear AND (a.stat_month >= 1 and a.stat_month <= @endMonth))
		OR (a.stat_year > @startYear AND a.stat_year < @endYear)
		OR (a.stat_year = @sQuayear AND (a.stat_month >= @sQuamonth and a.stat_month <= @eQuamonth)))
		ORDER BY a.stat_year,a.stat_month,a.larea_pk,a.score DESC
	END



--整理@temp表中的排名
BEGIN
	SET @delcount = 1
	SET @cnt = (SELECT count(1) FROM @temp)
	WHILE @cnt > 0
	BEGIN
		SELECT TOP 1 @cur_year = stat_year
			, @cur_month = stat_month
			, @cur_larea = larea_pk
			, @cur_sarea = sarea_pk
			, @cur_dealer = dealer_pk
			, @cur_score = score
			, @cur_sort=sort
		FROM @temp ORDER BY sort

		IF @cur_year<> @old_year OR @cur_month <> @old_month  OR @cur_larea <> @old_larea-- 公共的条件
		BEGIN
			SET @delcount = @cur_sort
		END
		
		IF @old_score = @cur_score --如果得分和上一名次的相同，则名次也是一样的
		BEGIN
			UPDATE VMCSS.t_after_mot_statistics SET larea_sort = @old_sort WHERE stat_year = @cur_year AND stat_month = @cur_month
			AND larea_pk = @cur_larea AND sarea_pk = @cur_sarea AND dealer_pk = @cur_dealer 
		END
		ELSE 
		BEGIN
			UPDATE VMCSS.t_after_mot_statistics SET larea_sort = @cur_sort - @delcount + 1 WHERE stat_year = @cur_year AND stat_month = @cur_month
			AND larea_pk = @cur_larea AND sarea_pk = @cur_sarea AND dealer_pk = @cur_dealer 
			SET @old_sort = @cur_sort - @delcount + 1	
		END
		
		SET @old_year = @cur_year
		SET @old_month = @cur_month
		SET @old_larea = @cur_larea
		SET @old_sarea = @cur_sarea
		SET @old_dealer = @cur_dealer
		SET @old_score = @cur_score
		
		DELETE FROM @temp WHERE sort = @cur_sort --删除这一条数据
		SET @cnt = @cnt - 1
	END
END
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO