300分解一问题，解决的话在给200分

2005-02-07 10:39:30 429 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

免安装版Notepad++终极配置：右键菜单+图标美化+多文件批量处理（2024新版）本文详细介绍了如何为免安装版Notepad++进行终极配置，包括右键菜单添加、图标美化以及多文件批量处理功能。通过注册表编辑和高级定制，用户可以在便携版中实现与安装版相同的功能体验，同时保持系统整洁。特别适合开发者、系统管理员和效率追求者使用。阅读详情

CREATE TABLE #TMP(JT VARCHAR(20) not null,bph varchar(10) not null,Sph Varchar(50) Null,quantity decimal(18,4) Null)
Insert Into #tmp
Select 'A','001','A001',20
Union all
Select 'A','001','A002',20
Union all
Select 'A','002','A003',23
Union All
Select 'B','001','A002',25
Union All
Select 'B','002','A002',25
Union All
Select 'C','001','A003',26
要求输出
JT bph Sph1 Sph2 quantity
A 001-002 A001 A002 43
B 001-002 A001 50
C 001 A003 26

...全文

拯救者R7000用户实测：用华为电脑管家11.0版搞定MatePad Pro多屏协同（附驱动与显卡避坑指南）本文详细介绍了如何在非华为笔记本联想拯救者R7000上安装华为电脑管家11.0版，实现与MatePad Pro的多屏协同功能。从驱动优化到显卡配置，提供了全面的避坑指南和实用技巧，帮助用户解决连接失败等常见问题，提升跨设备生产力体验。阅读详情

lllqe

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

Shires 2005-02-10

打赏
举报

zb1999 2005-02-10

打赏
举报

zjcxc 元老 2005-02-10

打赏
举报

--加多一个判断就行了.

CREATE TABLE tb(JT VARCHAR(20) not null,bph varchar(10) not null,Sph Varchar(50) Null,quantity decimal(18,4) Null)
Insert tb Select 'A','001','A001',20
Union all Select 'A','001','A002',20
Union all Select 'A','001','A003',20
Union all Select 'A','001','A004',20
Union all Select 'A','002','A004',23
Union All Select 'B','001','A002',25
Union All Select 'B','002','A002',25
Union All Select 'C','001','A003',26
go

--处理(假定不会出现 bph 不连续的情况)
declare @s nvarchar(4000),@i varchar(10)
select top 1
@s=N'',@i=count(*)
from tb group by Sph
order by count(*) desc
while @i>0
select @s=N',Sph'+@i+N'=max(case i when '+@i+' then Sph else N'''' end)'+@s
,@i=@i-1
exec('select JT
,bph=case when min(bph)=max(bph) then rtrim(min(bph)) else rtrim(min(bph)) +N''-''+rtrim(max(bph)) end
'+@s+N'
,quantity=sum(quantity)
from(
select i=(select count(distinct Sph) from tb where JT=a.JT and Sph<a.Sph)+1
,*
from tb a
)aa group by JT')
go

--删除测试
drop table tb

lvwaike 2005-02-08

打赏
举报

都写那么多呀，太厉害呀

子陌红尘 2005-02-07

打赏
举报

declare @s varchar(8000)
declare @i varchar(10)
set @s = ''

select @i = max(a.counts) from (select count(distinct Sph) as counts from #tmp group by JT) a

while(@i>0)
begin
select @s=',[sph'+@i+']=max(case when id='+@i+' then sph end) '+@s
set @i=@i-1
end

exec('
select
JT,
Sph,
min(bph) as bph1,
max(bph) as bph2,
sum(quantity) as quantity,
id = 0
into
#t
from
#tmp
group by JT,Sph

declare @i int,@Sph varchar(50)

update #t set @i=case when @Sph=Sph then @i+1 else 1 end,id=@i,@Sph=Sph

select JT,
case
when min(bph1) = max(bph2) then min(bph1)
else min(bph1)+''-''+max(bph2)
end as bph
'+@s+',sum(quantity) as quantity from #t group by JT')

zjcxc 元老 2005-02-07

打赏
举报

--考虑 bph 不连续的处理

--测试数据
CREATE TABLE tb(JT VARCHAR(20) not null,bph varchar(10) not null,Sph Varchar(50) Null,quantity decimal(18,4) Null)
Insert tb Select 'A','001','A001',20
Union all Select 'A','002','A002',20
Union all Select 'A','004','A003',23
Union All Select 'B','001','A002',25
Union All Select 'B','002','A002',25
Union All Select 'C','001','A003',26
go

--处理函数
create function f_bph(@JT VARCHAR(20))
returns nvarchar(4000)
as
begin
declare @re nvarchar(4000),@pid int
select @re='',@pid=-1
select @re=case @pid+1 when id then @re
else @re+N'-'+right(1000+@pid,3)+','+right(1000+id,3) end
,@pid=id
from(
select distinct id=cast(bph as int)
from tb where JT=@JT and isnumeric(bph)=1
)a order by id
return(stuff(@re,1,5,N'')+case when right(@re,3)=@pid then N'' else N'-'+right(1000+@pid,3) end)
end
go

--处理(假定不会出现 bph 不连续的情况)
declare @s nvarchar(4000),@i varchar(10)
select top 1
@s=N'',@i=count(*)
from tb group by Sph
order by count(*) desc
while @i>0
select @s=N',Sph'+@i+N'=max(case i when '+@i+' then Sph else N'''' end)'+@s
,@i=@i-1
exec('select JT
,bph=dbo.f_bph(JT)
'+@s+N'
,quantity=sum(quantity)
from(
select i=(select count(distinct Sph) from tb where JT=a.JT and Sph<a.Sph)+1
,*
from tb a
)aa group by JT')
go

--删除测试
drop table tb
drop function f_bph

/*--测试结果

JT bph Sph1 Sph2 Sph3 quantity
-------------------- ----------- ---------- ------------ ----------
A 001-002,004 A001 A002 A003 63.0000
B 001-002 A002 50.0000
C 001 A003 26.0000
--*/

zjcxc 元老 2005-02-07

打赏
举报

--测试数据
CREATE TABLE tb(JT VARCHAR(20) not null,bph varchar(10) not null,Sph Varchar(50) Null,quantity decimal(18,4) Null)
Insert tb Select 'A','001','A001',20
Union all Select 'A','001','A002',20
Union all Select 'A','002','A003',23
Union All Select 'B','001','A002',25
Union All Select 'B','002','A002',25
Union All Select 'C','001','A003',26
go

--处理(假定不会出现 bph 不连续的情况)
declare @s nvarchar(4000),@i varchar(10)
select top 1
@s=N'',@i=count(*)
from tb group by Sph
order by count(*) desc
while @i>0
select @s=N',Sph'+@i+N'=max(case i when '+@i+' then Sph else N'''' end)'+@s
,@i=@i-1
exec('select JT
,bph=rtrim(min(bph))+N''-''+rtrim(max(bph))
'+@s+N'
,quantity=sum(quantity)
from(
select i=(select count(distinct Sph) from tb where JT=a.JT and Sph<a.Sph)+1
,*
from tb a
)aa group by JT')
go

--删除测试
drop table tb

/*--测试结果

JT bph Sph1 Sph2 Sph3 quantity
-------------------- ----------- ---------- ------------ ----------
A 001-002 A001 A002 A003 63.0000
B 001-002 A002 50.0000
C 001-001 A003 26.0000
--*/

zjcxc 元老 2005-02-07

打赏
举报

bph：同一jt,它们的bph合在一起，如A:它有两个pbh,就是转换成 '001-002'

如果有不连续的怎么合并?

lllqe 2005-02-07

打赏
举报

bph：同一jt,它们的bph合在一起，如 jt A 它有pbh '001','002','003','004'
则
jt bph
A 001-004

lllqe 2005-02-07

打赏
举报

不好意思，我说得不够详细拉
quantity:这个字段是根据 jt 分组的
bph：同一jt,它们的bph合在一起，如A:它有两个pbh,就是转换成 '001-002'
Sph1 Sph2 Sph3 是不限制字段，它是根据 jt来确定的.
输出格式应该是：

JT bph Sph1 Sph2 Sph3 quantity
A 001-002 A001 A002 A003 63
B 001-002 A002 50
C 001 A003 26

子陌红尘 2005-02-07

打赏
举报

Sph1、Sph2字段的获取规则总应该有个交代吧，况且楼主给出的数据和期望的输出之间还有矛盾。

dzhfly 2005-02-07

打赏
举报

select JT,cast(min(bph)as nvarchar(10))+
(case when cast(min(bph)as nvarchar(10))=cast(max(bph)as nvarchar(10)) then ''
else +'-'+cast(max(bph)as nvarchar(10)) end )
as bph,
cast(JT+min(bph)as nvarchar(10)) as Sph1,
(case when cast(min(bph)as nvarchar(10))=cast(max(bph)as nvarchar(10)) then ''
else cast(JT+max(bph)as nvarchar(10)) end )
as Sph2,
cast(sum(quantity)as numeric(8,0)) as quantity
from #TMP group by JT

renaski 2005-02-07

打赏
举报

说下规则吧。

zjcxc 元老 2005-02-07

打赏
举报

看不出什么意思

Frewin 2005-02-07

打赏
举报

是不是
JT bph Sph1 Sph2 Sph3 quantity
A 001-002 A001 A002 A003 63
B 001-002 A001 50
C 001 A003 26

jinjazz 2005-02-07

打赏
举报

每看出规则？A的'A003'为什么不统计？还有B怎么来的A001

子陌红尘 2005-02-07

打赏
举报

修改一下邹老大的：

--------------------------------------------------------------------------------
--测试数据
CREATE TABLE tb(JT VARCHAR(20) not null,bph varchar(10) not null,Sph Varchar(50) Null,quantity decimal(18,4) Null)
Insert tb Select 'A','001','A001',20
Union all Select 'A','001','A002',20
Union all Select 'A','002','A003',23
Union All Select 'B','001','A002',25
Union All Select 'B','002','A002',25
Union All Select 'C','001','A003',26
go

--处理(假定不会出现 bph 不连续的情况)
declare @s nvarchar(4000),@i varchar(10)
select top 1
@s=N'',@i=count(*)
from tb group by Sph
order by count(*) desc
while @i>0
select @s=N',Sph'+@i+N'=max(case i when '+@i+' then Sph else N'''' end)'+@s
,@i=@i-1
exec('select JT
,bph=(case when min(bph) = max(bph) then min(bph) else rtrim(min(bph))+N''-''+rtrim(max(bph)) end)
'+@s+N'
,quantity=sum(quantity)
from(
select i=(select count(distinct Sph) from tb where JT=a.JT and Sph<a.Sph)+1
,*
from tb a
)aa group by JT')
go

--删除测试
drop table tb

子陌红尘 2005-02-07

打赏
举报

修改一下：
-----------------------------------------------------------------------------------
CREATE TABLE #TMP(JT VARCHAR(20) not null,bph varchar(10) not null,Sph Varchar(50) Null,quantity decimal(18,4) Null)
Insert Into #tmp
Select 'A','001','A001',20
Union all
Select 'A','001','A002',20
Union all
Select 'A','002','A003',23
Union All
Select 'B','001','A002',25
Union All
Select 'B','002','A002',25
Union All
Select 'C','001','A003',26

declare @s varchar(8000)
declare @i varchar(10)
set @s = ''

select @i = max(a.counts) from (select count(distinct Sph) as counts from #tmp group by JT) a

while(@i>0)
begin
select @s=',[sph'+@i+']=max(case when id='+@i+' then sph end) '+@s
set @i=@i-1
end

exec('
select
JT,
Sph,
min(bph) as bph1,
max(bph) as bph2,
sum(quantity) as quantity,
id = 0
into
#t
from
#tmp
group by JT,Sph
order by JT,Sph

declare @i int,@JT varchar(50)

update #t set @i=case when @JT=JT then @i+1 else 1 end,id=@i,@JT=JT

select JT,
case
when min(bph1) = max(bph2) then min(bph1)
else min(bph1)+''-''+max(bph2)
end as bph
'+@s+',sum(quantity) as quantity from #t group by JT')

drop table #tmp

lllqe 2005-02-07

打赏
举报

谢谢zjcxc(邹建) ( ) 信誉：492 ，不过有点问题

lllqe 2005-02-07

打赏
举报

有点问题？
CREATE TABLE tb(JT VARCHAR(20) not null,bph varchar(10) not null,Sph Varchar(50) Null,quantity decimal(18,4) Null)
Insert tb Select 'A','001','A001',20
Union all Select 'A','001','A002',20
Union all Select 'A','001','A003',20
Union all Select 'A','001','A004',20
Union all Select 'A','002','A004',23
Union All Select 'B','001','A002',25
Union All Select 'B','002','A002',25
Union All Select 'C','001','A003',26
go

--处理(假定不会出现 bph 不连续的情况)
declare @s nvarchar(4000),@i varchar(10)
select top 1
@s=N'',@i=count(*)
from tb group by Sph
order by count(*) desc
while @i>0
select @s=N',Sph'+@i+N'=max(case i when '+@i+' then Sph else N'''' end)'+@s
,@i=@i-1
exec('select JT
,bph=rtrim(min(bph))+N''-''+rtrim(max(bph))
'+@s+N'
,quantity=sum(quantity)
from(
select i=(select count(distinct Sph) from tb where JT=a.JT and Sph<a.Sph)+1
,*
from tb a
)aa group by JT')
go

--删除测试
drop table tb

300分解一问题，解决的话在给200分

相关推荐