27,579
社区成员
发帖
与我相关
我的任务
分享
if object_id('TableA') is not null
drop table TableA
go
create table TableA (customerId int,country varchar(3),sales numeric(18,2),sale numeric(18,2),abc varchar(1))
go
insert into TableA
select 1,'CHN',40,0,'' union all
select 2,'CHN',20,0,'' union all
select 3,'CHN',15,0,'' union all
select 4,'CHN',5,0,'' union all
select 5,'CHN',4,0,'' union all
select 6,'CHN',4,0,'' union all
select 7,'CHN',4,0,'' union all
select 8,'CHN',4,0,'' union all
select 9,'CHN',3,0,'' union all
select 10,'CHN',1,0,'' union all
select 1,'JAP',38,0,'' union all
select 2,'JAP',20,0,'' union all
select 3,'JAP',15,0,'' union all
select 4,'JAP',7,0,'' union all
select 5,'JAP',5,0,'' union all
select 6,'JAP',4,0,'' union all
select 7,'JAP',4,0,'' union all
select 8,'JAP',4,0,'' union all
select 9,'JAP',2,0,'' union all
select 10,'JAP',1,0,''
go
select * from TableA
--执行前的数据
/*--------------------------------------
1 CHN 40.00 0.00
2 CHN 20.00 0.00
3 CHN 15.00 0.00
4 CHN 5.00 0.00
5 CHN 4.00 0.00
6 CHN 4.00 0.00
7 CHN 4.00 0.00
8 CHN 4.00 0.00
9 CHN 3.00 0.00
10 CHN 1.00 0.00
1 JAP 38.00 0.00
2 JAP 20.00 0.00
3 JAP 15.00 0.00
4 JAP 7.00 0.00
5 JAP 5.00 0.00
6 JAP 4.00 0.00
7 JAP 4.00 0.00
8 JAP 4.00 0.00
9 JAP 2.00 0.00
10 JAP 1.00 0.00
*/
if OBJECT_ID('UpdateSeq','p') is not null
drop proc UpdateSeq
go
create proc UpdateSeq
as
select * into #temp from TableA order by country,sale desc
update #temp set sale=(select convert(float,a.sales)/SUM(b.sales) from #temp b where a.country=b.country group by country)
from #temp a
declare @country varchar(20),@sale numeric(18,2),@abc varchar(20)
select @country='',@sale=0,@abc=''
update #temp set @sale=case when @country=country then @sale+sale else sale end,
@country=country,
@abc=abc=case when @sale<=0.8 then 'A'
when @sale between 0.8 and 0.8+0.2*0.8 then 'B'
else 'C' end
update TableA set sale=b.sale,abc=b.abc from #temp b where TableA.customerId=b.customerId
drop table #temp
select * from TableA
return
go
exec UpdateSeq
go
--执行后的结果
/*-------------------------------
1 CHN 40.00 0.40 A
2 CHN 20.00 0.20 A
3 CHN 15.00 0.15 A
4 CHN 5.00 0.05 A
5 CHN 4.00 0.04 B
6 CHN 4.00 0.04 B
7 CHN 4.00 0.04 B
8 CHN 4.00 0.04 B
9 CHN 3.00 0.03 C
10 CHN 1.00 0.01 C
1 JAP 38.00 0.40 A
2 JAP 20.00 0.20 A
3 JAP 15.00 0.15 A
4 JAP 7.00 0.05 A
5 JAP 5.00 0.04 B
6 JAP 4.00 0.04 B
7 JAP 4.00 0.04 B
8 JAP 4.00 0.04 B
9 JAP 2.00 0.03 C
10 JAP 1.00 0.01 C
*/
;with cte as
(
select customerId,country,sales,
row_number() over(partition by country order by country,sales desc) rn,
Sum(sales) over(partition by country ) T
from [A]
)
select customerId,country,sales,rn,
cast((100.0*sales/T) as decimal(18,2)) as [sales%],
case when (select IsNull(sum(sales),0) from cte where rn<=tb.rn and tb.country=country)/T <=0.8 then 'A'
when (select IsNull(sum(sales),0) from cte where rn>tb.rn and tb.country=country)/(T*.2) <0.2 then 'C'
else 'B' end ABC
from cte tb
1 CHN 40.00 1 40.00 A
2 CHN 20.00 2 20.00 A
3 CHN 15.00 3 15.00 A
4 CHN 5.00 4 5.00 A
5 CHN 4.00 5 4.00 B
6 CHN 4.00 6 4.00 B
7 CHN 4.00 7 4.00 B
8 CHN 4.00 8 4.00 B
9 CHN 3.00 9 3.00 C
10 CHN 1.00 10 1.00 C
1 JAP 38.00 1 38.00 A
2 JAP 20.00 2 20.00 A
3 JAP 15.00 3 15.00 A
4 JAP 7.00 4 7.00 A
5 JAP 5.00 5 5.00 B
6 JAP 4.00 6 4.00 B
7 JAP 4.00 7 4.00 B
8 JAP 4.00 8 4.00 C
9 JAP 2.00 9 2.00 C
10 JAP 1.00 10 1.00 C
--> 测试数据:[customerABC]
if object_id('[customerABC]') is not null drop table [customerABC]
go
create table [customerABC]([customerId] int,[country] varchar(3),[sales] int,[sale%] varchar(20),[customer] varchar(20))
insert [customerABC]([customerId],[country],[sales])
select 1,'CHN',40 union all
select 2,'CHN',20 union all
select 3,'CHN',15 union all
select 4,'CHN',5 union all
select 5,'CHN',4 union all
select 6,'CHN',4 union all
select 7,'CHN',4 union all
select 8,'CHN',4 union all
select 9,'CHN',3 union all
select 10,'CHN',1 union all
select 1,'JAP',38 union all
select 2,'JAP',20 union all
select 3,'JAP',15 union all
select 4,'JAP',7 union all
select 5,'JAP',5 union all
select 6,'JAP',4 union all
select 7,'JAP',4 union all
select 8,'JAP',4 union all
select 9,'JAP',2 union all
select 10,'JAP',1
update t
set [sale%] = ltrim(cast(sales*100.0/(select sum(sales) from customerABC where country = t.country) as dec(9,0))) + '%',
customer = case when
(select sum(sales) from customerABC where country = t.country and customerId <= t.customerId)*1.0/(select sum(sales) from customerABC where country = t.country) <= 0.8
then 'A' else 'B' end
from customerABC t
update t
set customer = 'C'
from customerABC t
where customer = 'B' and
(select sum(sales) from customerABC where country = t.country and customerId <= t.customerId and customer = 'B')*1.0/(select sum(sales) from customerABC where country = t.country and customer = 'B') >= 0.8
select * from customerABC
customerId country sales sale% customer
----------- ------- ----------- -------------------- --------------------
1 CHN 40 40% A
2 CHN 20 20% A
3 CHN 15 15% A
4 CHN 5 5% A
5 CHN 4 4% B
6 CHN 4 4% B
7 CHN 4 4% B
8 CHN 4 4% C
9 CHN 3 3% C
10 CHN 1 1% C
1 JAP 38 38% A
2 JAP 20 20% A
3 JAP 15 15% A
4 JAP 7 7% A
5 JAP 5 5% B
6 JAP 4 4% B
7 JAP 4 4% B
8 JAP 4 4% C
9 JAP 2 2% C
10 JAP 1 1% C
(所影响的行数为 20 行)
if object_id('[A]') is not null drop table [A]
create table [A] (customerId int,country varchar(3),sales decimal(18,2),sale varchar(3),abc varchar(1))
insert into [A]
select 1,'CHN',40,'40%','A' union all
select 2,'CHN',20,'20%','A' union all
select 3,'CHN',15,'15%','A' union all
select 4,'CHN',5,'5%','A' union all
select 5,'CHN',4,'4%','B' union all
select 6,'CHN',4,'4%','B' union all
select 7,'CHN',4,'4%','B' union all
select 8,'CHN',4,'4%','B' union all
select 9,'CHN',3,'3%','C' union all
select 10,'CHN',1,'1%','C' union all
select 1,'JAP',38,'38%','A' union all
select 2,'JAP',20,'20%','A' union all
select 3,'JAP',15,'15%','A' union all
select 4,'JAP',7,'7%','A' union all
select 5,'JAP',5,'5%','B' union all
select 6,'JAP',4,'4%','B' union all
select 7,'JAP',4,'4%','B' union all
select 8,'JAP',4,'4%','B' union all
select 9,'JAP',2,'2%','C' union all
select 10,'JAP',1,'1%','C'
select customerId,country,sales,[sales%] ,
(case when num<=0.8 then 'A'
when Num<=0.9 and Num>=0.8 then 'B'
else 'C' end) abc
from
(
select customerId,country,sales,
cast(100.0*sales/sum(sales) over(partition by country) as decimal(18,2)) as [sales%],
row_number() over(partition by country order by country,sales)*1.00 /count(*) over(partition by country) num
from [A]
) T
customerId country sales sales% abc
----------- ------- --------------------------------------- --------------------------------------- ----
10 CHN 1.00 1.00 A
9 CHN 3.00 3.00 A
5 CHN 4.00 4.00 A
6 CHN 4.00 4.00 A
7 CHN 4.00 4.00 A
8 CHN 4.00 4.00 A
4 CHN 5.00 5.00 A
3 CHN 15.00 15.00 A
2 CHN 20.00 20.00 B
1 CHN 40.00 40.00 C
10 JAP 1.00 1.00 A
9 JAP 2.00 2.00 A
6 JAP 4.00 4.00 A
7 JAP 4.00 4.00 A
8 JAP 4.00 4.00 A
5 JAP 5.00 5.00 A
4 JAP 7.00 7.00 A
3 JAP 15.00 15.00 A
2 JAP 20.00 20.00 B
1 JAP 38.00 38.00 C
(20 行受影响)