本文共 6484 字，大约阅读时间需要 21 分钟。
SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOCREATE PROCEDURE [dbo].[proc_mot_sort] (@startYear int , @startMonth int , @endYear int , @endMonth int , @sQuayear int , @sQuamonth int , @eQuayear int , @eQuamonth int ) ASBEGIN --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 ENDEND -- 查询经销商在大区范围内要更新排名的得分 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 ENDENDENDGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
转载地址：http://xedpi.baihongyu.com/