求助一个存储过程不知道怎么写

shenqdm 2010-07-08 01:41:03
一个 customerABC 表
结构如下
customerId |country | sales | sale% |customer ABC

客户ID | 国家 | 销售额 | 销售额百分比 |客户类别ABC

客户ID ,国家 ,销售额 都是已知的,客户类别customer ABCABC需要存储过程计算
要求:销售额根据每个国家groupby 降序排列 ,根据客户的销售额,算出这个客户的销售额在总的这个国家销售额所占的比例,然后占前80%的客户为A类客户,占余下的中80%的为B类客户,剩下的都为C类客户,把算出的ABC填入customer ABC
列中
前80%,不一定是整数80%比如算到第九个客户的时候是79%第十个的时候是81%那么第十个就算A客户
同样计算B客户也是如此
如下表
求算这个customer ABC的存储过程怎么写
customerId |country | sales | sale% |customer ABC
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% B
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% B
9 JAP 2 2% C
10 JAP 1 1% C

...全文
215 19 打赏 收藏 转发到动态 举报
写回复
用AI写文章
19 条回复
切换为时间正序
请发表友善的回复…
发表回复
shenqdm 2010-07-14
  • 打赏
  • 举报
回复
大虾们都到哪去了
shenqdm 2010-07-12
  • 打赏
  • 举报
回复
没有人能回答吗
shenqdm 2010-07-11
  • 打赏
  • 举报
回复
感谢大家的回复由于这2天出差去了,所以一直还没回复
对于 nianran520 的回复有2个问题 ,首先表里的sales不是像我给的数据那样是自动降序排列的,所以sql里还要对他进行排序
其次当一个A类中排下来排到一个是79% ,但下面那个是83%那么下面那个也因排为A类,B类也同样如此,
小弟对于sql并不是很熟悉,麻烦大家帮助下,得到解答马上给分
shenqdm 2010-07-11
  • 打赏
  • 举报
回复
[Quote=引用 7 楼 nianran520 的回复:]
SQL code
--> 测试数据:[customerABC]
if object_id('[customerABC]') is not null drop table [customerABC]
go
create table [customerABC]([customerId] int,[country] varchar(3),[sales] int,[sale%] varchar(2……
[/Quote]
ssh87 2010-07-11
  • 打赏
  • 举报
回复
[Quote=引用 11 楼 nightmaple 的回复:]
SQL code
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 ……
[/Quote]///
shenqdm 2010-07-11
  • 打赏
  • 举报
回复
另外个就是我前面说的其次当一个A类中排下来排到一个是79% ,但下面那个是83%那么下面那个也因排为A类,B类也同样如此
shenqdm 2010-07-11
  • 打赏
  • 举报
回复
那个是正确的但是我给的数据sales是降序排列的但是实际数据是没有秩序的,所以再code中首先要sales排序,而且排序后customerId <= t.customerId 这种写法是不能用了,可能是我给数据的时候没说清楚
永生天地 2010-07-11
  • 打赏
  • 举报
回复
[Quote=引用 13 楼 shenqdm 的回复:]
感谢大家的回复由于这2天出差去了,所以一直还没回复
对于 nianran520 的回复有2个问题 ,首先表里的sales不是像我给的数据那样是自动降序排列的,所以sql里还要对他进行排序
其次当一个A类中排下来排到一个是79% ,但下面那个是83%那么下面那个也因排为A类,B类也同样如此,
小弟对于sql并不是很熟悉,麻烦大家帮助下,得到解答马上给分
[/Quote]
你先确定一下,那个是否正确,似乎不会那么简单
nightmaple 2010-07-08
  • 打赏
  • 举报
回复
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
*/
bancxc 2010-07-08
  • 打赏
  • 举报
回复

;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
东那个升 2010-07-08
  • 打赏
  • 举报
回复
[Quote=引用 4 楼 bancxc 的回复:]
SQL code
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,'4……
[/Quote]

太阳狼哥
bancxc 2010-07-08
  • 打赏
  • 举报
回复
ken威武
nianran520 2010-07-08
  • 打赏
  • 举报
回复
--> 测试数据:[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 行)
水族杰纶 2010-07-08
  • 打赏
  • 举报
回复
[Quote=引用 4 楼 bancxc 的回复:]
SQL code
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,'4……
[/Quote]
顶狼哥
bancxc 2010-07-08
  • 打赏
  • 举报
回复
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 行受影响)
冷箫轻笛 2010-07-08
  • 打赏
  • 举报
回复
哦,我明白了
比如到第9个客户是79% ,那即使是第10个客户到100% ,那第10个客户也算A,对吧?
冷箫轻笛 2010-07-08
  • 打赏
  • 举报
回复
前80%,不一定是整数80%比如算到第九个客户的时候是79%第十个的时候是81%那么第十个就算A客户

这个没搞明白,

81% 算A,那82%呢? 83 / 84 / 85呢?......
SQL77 2010-07-08
  • 打赏
  • 举报
回复
貌似2005以上的NLINE很容易解决此类问题

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧