27,579
社区成员
发帖
与我相关
我的任务
分享
if object_id('spls') is not null drop table spls
create table spls(单据编号 nvarchar(20),商品名称 nvarchar(100), 定价 decimal(14,2), 数量 decimal(14,2),操作时间 datetime,操作说明 nvarchar(100))
insert spls
select 'PC20090600071', '我学画·儿童趣味简笔画:幼儿学画(动物-狮子)', 8 , 2 , '06/01/2009 13:33:20', '移转上架:张三+王五+李四 '
union all select 'PC20090600071', '幼儿学画:植物 ', 8.8 , 4 , '05/31/2009 13:33:20', '移转上架:张三+王五+李四 '
union all select 'PC20090600071', '幼儿学画:水果蔬菜 ', 8.8 , 15 , '05/31/2009 13:33:20', '移转上架:张三+王五+李四'
union all select 'PU20090507534', '在科学的入口处30位能源科学家的贡献 ', 14.8 , 0 , '05/31/2009 17:40:49', '上架:王五+李四 '
union all select 'PU20090507534', '在科学的入口处30位能源科学家的贡献 ', 14.8 ,4 , '05/31/2009 17:40:49', '上架:王五+李四 '
union all select 'PU20090507534', '在科学的入口处30位物理学家的贡献 ', 14.8 ,0 , '05/31/2009 17:40:49', '上架:王五+李四 '
union all select 'PU20090507534', '在科学的入口处30位物理学家的贡献 ', 14.8 ,4 , '05/31/2009 17:40:49', '上架:王五+李四'
union all select 'PU20090507166', '我最喜欢的儿童书:妈妈讲故事(绿色卷) ', 9.8 ,1 , '05/31/2009 10:52:53', '上架:李四 '
union all select 'PU20090507166', '我最喜欢的儿童书:睡前好故事(黄色卷) ', 9.8 ,0 , '05/31/2009 10:52:53', '上架:李四 '
union all select 'PU20090507022', '让宝宝受益一生的中国童话经典:小猫钓鱼 ', 5 ,15 , '05/31/2009 10:43:53', '上架:马六+李四 '
union all select 'PU20090507022', '让宝宝受益一生的中国童话经典:小猫钓鱼 ', 5 ,178 , '05/31/2009 10:43:53', '上架:马六+李四 '
union all select 'PU20090507022', '让宝宝受益一生的中国童话经典:小猫钓鱼 ', 5 ,1 , '05/31/2009 10:43:53', '上架:马六+李四'
update spls set 操作说明=STUFF(操作说明,1,charindex(':',操作说明),N'')
--使用辅助表方法处理
-->>>>1.根据SPLS表中操作说明字段的最大值创建表
declare @len int
select top 1 @len=LEN(操作说明)+1
from spls
order by LEN(操作说明) desc
--如果没有要分拆的数据,不需要处理
if isnull(@len,0)=0
return
--创建辅助表
set rowcount @len --设置操作所影响的行数
SELECT ID=IDENTITY(INT,1,1) INTO FCFZB --分拆辅助表
from dbo.sysobjects a,dbo.syscolumns --无条件连接
--设置主健
alter table FCFZB add primary key(id)
set rowcount 0 --恢复操作所影响的行数,默认值(有多少显示多少)
--正式处理
select 操作员,count(distinct(单据编号)) as 单据数,count(distinct(商品名称)) as 品种数,count(*) as 出现次数,SUM(平均数) AS 产量
from
(select 操作员=substring(a.操作说明,b.id,CHARINDEX('+',a.操作说明+'+',b.id)-b.id),
a.数量,平均数=cast(a.数量/(LEN(a.操作说明)-LEN(REPLACE(a.操作说明,'+',''))+1) as decimal(14,2)),
a.单据编号,a.商品名称
from spls a,FCFZB b
where b.id<=LEN(a.操作说明)
and SUBSTRING('+'+a.操作说明,b.id,1)='+')a
group by a.操作员
/*
操作员 单据数 品种数 出现次数 产量
李四 4 8 12 109.00
马六 1 1 3 97.00
王五 2 5 7 11.00
张三 1 3 3 7.00
*/
if object_id('spls') is not null drop table spls
create table spls(单据编号 nvarchar(20),商品名称 nvarchar(100), 定价 decimal(14,2), 数量 decimal(14,2),操作时间 datetime,操作说明 nvarchar(100))
insert spls
select 'PC20090600071', '我学画·儿童趣味简笔画:幼儿学画(动物-狮子)', 8 , 2 , '06/01/2009 13:33:20', '移转上架:张三+王五+李四 '
union all select 'PC20090600071', '幼儿学画:植物 ', 8.8 , 4 , '05/31/2009 13:33:20', '移转上架:张三+王五+李四 '
union all select 'PC20090600071', '幼儿学画:水果蔬菜 ', 8.8 , 15 , '05/31/2009 13:33:20', '移转上架:张三+王五+李四'
union all select 'PU20090507534', '在科学的入口处30位能源科学家的贡献 ', 14.8 , 0 , '05/31/2009 17:40:49', '上架:王五+李四 '
union all select 'PU20090507534', '在科学的入口处30位能源科学家的贡献 ', 14.8 ,4 , '05/31/2009 17:40:49', '上架:王五+李四 '
union all select 'PU20090507534', '在科学的入口处30位物理学家的贡献 ', 14.8 ,0 , '05/31/2009 17:40:49', '上架:王五+李四 '
union all select 'PU20090507534', '在科学的入口处30位物理学家的贡献 ', 14.8 ,4 , '05/31/2009 17:40:49', '上架:王五+李四'
union all select 'PU20090507166', '我最喜欢的儿童书:妈妈讲故事(绿色卷) ', 9.8 ,1 , '05/31/2009 10:52:53', '上架:李四 '
union all select 'PU20090507166', '我最喜欢的儿童书:睡前好故事(黄色卷) ', 9.8 ,0 , '05/31/2009 10:52:53', '上架:李四 '
union all select 'PU20090507022', '让宝宝受益一生的中国童话经典:小猫钓鱼 ', 5 ,15 , '05/31/2009 10:43:53', '上架:马六+李四 '
union all select 'PU20090507022', '让宝宝受益一生的中国童话经典:小猫钓鱼 ', 5 ,178 , '05/31/2009 10:43:53', '上架:马六+李四 '
union all select 'PU20090507022', '让宝宝受益一生的中国童话经典:小猫钓鱼 ', 5 ,1 , '05/31/2009 10:43:53', '上架:马六+李四'
--游标中会将表中插入记录如张三+王五+李四分开生成三条记录
if object_id('spls2') is not null drop table spls2
create table spls2(单据编号 nvarchar(20),商品名称 nvarchar(100), 定价 decimal(14,2), 数量 decimal(14,2),操作时间 datetime,操作说明 nvarchar(100))
go
insert spls2 select * from spls where charindex('+',操作说明)<1
update spls set 操作说明=STUFF(操作说明,1,charindex(':',操作说明),N'')
go
--张三+王五+李四分开生成记录处理
DECLARE
@DJBH nvarchar(20),@SPMCH nvarchar(100),@DJ decimal(14,2),@SHL decimal(14,2),@times datetime,@beizhu nvarchar(100)
declare
@beizhu2 nvarchar(100),@sh decimal(14,2),@num decimal(14,2),@beizhu3 nvarchar(100)
DECLARE Bti_cursor CURSOR
FOR
select * from spls where charindex('+',操作说明)>0 --drop table spls2
open Bti_cursor
FETCH NEXT FROM Bti_cursor into @DJBH,@SPMCH,@DJ,@SHL,@times,@beizhu
WHILE @@FETCH_STATUS = 0
BEGIN
set @sh=0
set @num=0
set @beizhu3=@beizhu
while charindex('+',@beizhu3+'+')>1 --均分数量
begin
select
@sh=@sh+1,
@beizhu3=stuff(@beizhu3,1,charindex('+',@beizhu3+'+'),N'')
end
--插入记录处理
while charindex('+',@beizhu+'+')>1
begin
select @beizhu2=substring(@beizhu,1,charindex('+',@beizhu+'+')-1),
@beizhu=stuff(@beizhu,1,charindex('+',@beizhu+'+'),N''),
@num=@shl/@sh
insert spls2 select @DJBH,@SPMCH,@DJ,@num,@times,@beizhu2
end
FETCH NEXT FROM Bti_cursor into @DJBH,@SPMCH,@DJ,@SHL,@times,@beizhu
END
CLOSE Bti_cursor
deallocate Bti_cursor
--显示结果
select a.操作说明,max(b.单据数),max(c.品种数),SUM(a.数量) as 总册数
from spls2 a
inner join
(select 操作说明,count(1) as 单据数 from (select distinct 操作说明,单据编号 from spls2) t0 group by 操作说明)b on a.操作说明=b.操作说明
inner join
(select 操作说明,count(1) as 品种数 from (select distinct 操作说明,商品名称 from spls2) t1 group by 操作说明)c on a.操作说明=c.操作说明
group by a.操作说明
/*
操作说明 (无列名) (无列名) 总册数
李四 4 8 109.00
马六 1 1 97.00
王五 2 5 11.00
张三 1 3 7.00
*/
游标处理记录,可能速度稍慢