SQL循环处理问题

[renyiqiu]

ID city       count   level
1   北京市     50             
2   北京市     20              
3   北京市     10
4   邯郸市     20
5   邯郸市     10
6   邯郸市     80   

上面表结构

level 暂时为空

level 有三个等级 高，中，低，当该条记录的count 在对应城市的占比在大于80%小于等于100%则为高，大于60%小于等于80%则为中 ，小于等于60%则为低
现在想要一个能更新 level 的存储过程

[renyiqiu]

引用 1 楼 Tiger_Zhao 的回复:

CREATE TABLE #temp(ID int, city nvarchar(3),[count] int, [level] nvarchar(1))

INSERT INTO #temp(ID,city,[count])
SELECT 1,N'北京市',50 UNION ALL
SELECT 2,N'北京市',20 UNION ALL
SELECT 3,N'北京市',10 UNION ALL
SELECT 4,N'邯郸市',20 UNION ALL
SELECT 5,N'邯郸市',10 UNION ALL
SELECT 6,N'邯郸市',80

-- 这里开始创建为存储过程
;WITH s AS (
    SELECT city,
           SUM([count]) total
      FROM #temp
  GROUP BY city
)
UPDATE #temp
   SET #temp.[level] = (CASE WHEN #temp.[count] >= (s.total*80/100) THEN
                             N'高'
                        WHEN #temp.[count] >= (s.total*60/100) THEN
                             N'中'
                        ELSE
                             N'低'
                        END)
  FROM #temp, s
 WHERE #temp.city = s.city
--存储过程结束

SELECT * FROM #temp

         ID city         count level
----------- ------ ----------- -----
          1 北京市          50 中
          2 北京市          20 低
          3 北京市          10 低
          4 邯郸市          20 低
          5 邯郸市          10 低
          6 邯郸市          80 中

这个执行效率能否再优化呢，我这边有50w左右的数据需要更新。

[Tiger_Zhao]

数据量在那里，没有什么好优化的。
除了在city列上建索引。

第一次全体更新只能这样了。
你应该考虑以后有数据变更的时候，实时按一个city来进行更新。

[Tiger_Zhao]

CREATE TABLE #temp(ID int, city nvarchar(3),[count] int, [level] nvarchar(1))

INSERT INTO #temp(ID,city,[count])
SELECT 1,N'北京市',50 UNION ALL
SELECT 2,N'北京市',20 UNION ALL
SELECT 3,N'北京市',10 UNION ALL
SELECT 4,N'邯郸市',20 UNION ALL
SELECT 5,N'邯郸市',10 UNION ALL
SELECT 6,N'邯郸市',80

-- 这里开始创建为存储过程
;WITH s AS (
    SELECT city,
           SUM([count]) total
      FROM #temp
  GROUP BY city
)
UPDATE #temp
   SET #temp.[level] = (CASE WHEN #temp.[count] >= (s.total*80/100) THEN
                             N'高'
                        WHEN #temp.[count] >= (s.total*60/100) THEN
                             N'中'
                        ELSE
                             N'低'
                        END)
  FROM #temp, s
 WHERE #temp.city = s.city
--存储过程结束

SELECT * FROM #temp

         ID city         count level
----------- ------ ----------- -----
          1 北京市          50 中
          2 北京市          20 低
          3 北京市          10 低
          4 邯郸市          20 低
          5 邯郸市          10 低
          6 邯郸市          80 中

[许晨旭]

create table #tb
(
	ID  int,
	city varchar(20),
	[count] int,
	[level] varchar(20)
)

insert into #tb (ID,city,[count])
select 1,'北京市',50 union all
select 2,'北京市',20 union all              
select 3,'北京市',10 union all
select 4,'邯郸市',20 union all
select 5,'邯郸市',10 union all
select 6,'邯郸市',80


;with cte as
(
	select a.ID,
		a.city,
		a.[count],
		case
			WHEN 1.0*a.[count]/b.sumcount>0.8 THEN '高'
			WHEN 1.0*a.[count]/b.sumcount>0.6 and 1.0*a.[count]/b.sumcount<0.8 THEN '中'
			WHEN 1.0*a.[count]/b.sumcount<0.6 THEN '低'
		END as [level]
	from #tb a
	inner join(
			select city,SUM([count]) as sumcount
			from #tb
			group by city
	) b on a.city=b.city
)

update #tb set [level]=cte.[level] from cte where cte.ID=#tb.ID


select * from #tb

ID          city                 count       level
----------- -------------------- ----------- --------------------
1           北京市                  50          中
2           北京市                  20          低
3           北京市                  10          低
4           邯郸市                  20          低
5           邯郸市                  10          低
6           邯郸市                  80          中

(6 行受影响)

[道玄希言]

兼容 SQL2000写法:

update tbl03
set levels = (case 
when ((T1.counts*100.0)/T2.sumc) > 80 then '高'
when (((T1.counts*100.0)/T2.sumc <= 80) and ((T1.counts*100)/T2.sumc > 60)) then '中'
when ((T1.counts*100.0)/T2.sumc) <= 60 then '低'
end)
from tbl03 AS T1
left join
(select city, sum(counts) as sumc from tbl03 group by city) AS T2
ON T1.city = T2.city