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

lllqe 2005-02-07 10:39:30
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
...全文
329 20 打赏 收藏 转发到动态 举报
写回复
用AI写文章
20 条回复
切换为时间正序
请发表友善的回复…
发表回复
Shires 2005-02-10
  • 打赏
  • 举报
回复
oh
zb1999 2005-02-10
  • 打赏
  • 举报
回复
up
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

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

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