源数据
kind number
k1 1
k1 2
k2 2目标数据
kind number 生成编号
k1 1 3-1
k1 2 3-2,3-3
k2 2 2-1,2-2对kind 汇总,针对number生成一个编号
kind number
k1 1
k1 2
k2 2目标数据
kind number 生成编号
k1 1 3-1
k1 2 3-2,3-3
k2 2 2-1,2-2对kind 汇总,针对number生成一个编号
create table 源数据
(kind varchar(10), number int)insert into 源数据
select 'k1',1 union all
select 'k1',2 union all
select 'k2',2
with t as
(select kind,number,
row_number() over(order by getdate()) 'rn' from 源数据),
u as
(select a.kind,a.number,
rtrim(b.s)+'-'+rtrim(c.number) 'bh'
from t a
left join
(select kind,sum(number) 's'
from 源数据
group by kind) b on a.kind=b.kind
outer apply
(select isnull((select sum(e.number) from t e where e.kind=a.kind and e.rn<a.rn),0)+d.number 'number'
from master.dbo.spt_values d
where d.type='P' and d.number between 1 and a.number) c
)
select a.kind,a.number,
stuff((select ','+b.bh from u b
where b.kind=a.kind and b.number=a.number
for xml path('')),1,1,'') '生成编号'
from u a
group by a.kind,a.number/*
kind number 生成编号
---------- ----------- -------------
k1 1 3-1
k1 2 3-2,3-3
k2 2 2-1,2-2(3 row(s) affected)
*/
k1 1
k1 1最后成数据是
k1 1 2-1,2-2针对这种异常数据没有办法处理了。
(kind varchar(10), number int)
-- test1
truncate table 源数据insert into 源数据
select 'k1',1 union all
select 'k1',1
with t as
(select kind,number,
row_number() over(order by getdate()) 'rn' from 源数据),
u as
(select a.kind,a.number,a.rn,
rtrim(b.s)+'-'+rtrim(c.number) 'bh'
from t a
left join
(select kind,sum(number) 's'
from 源数据 group by kind) b on a.kind=b.kind
outer apply
(select isnull((select sum(e.number) from t e
where e.kind=a.kind and e.rn<a.rn),0)+d.number 'number'
from master.dbo.spt_values d
where d.type='P' and d.number between 1 and a.number) c
)
select a.kind,a.number,
stuff((select ','+b.bh from u b
where b.kind=a.kind and b.number=a.number and b.rn=a.rn
for xml path('')),1,1,'') '生成编号'
from u a
group by a.kind,a.number,a.rn
/*
kind number 生成编号
---------- ----------- --------------
k1 1 2-1
k1 1 2-2(2 row(s) affected)
*/
-- test2
truncate table 源数据insert into 源数据
select 'k1',1 union all
select 'k1',2 union all
select 'k2',2
with t as
(select kind,number,
row_number() over(order by getdate()) 'rn' from 源数据),
u as
(select a.kind,a.number,a.rn,
rtrim(b.s)+'-'+rtrim(c.number) 'bh'
from t a
left join
(select kind,sum(number) 's'
from 源数据 group by kind) b on a.kind=b.kind
outer apply
(select isnull((select sum(e.number) from t e
where e.kind=a.kind and e.rn<a.rn),0)+d.number 'number'
from master.dbo.spt_values d
where d.type='P' and d.number between 1 and a.number) c
)
select a.kind,a.number,
stuff((select ','+b.bh from u b
where b.kind=a.kind and b.number=a.number and b.rn=a.rn
for xml path('')),1,1,'') '生成编号'
from u a
group by a.kind,a.number,a.rn
/*
kind number 生成编号
---------- ----------- ----------------
k1 1 3-1
k1 2 3-2,3-3
k2 2 2-1,2-2(3 row(s) affected)
*/
(kind varchar(10), number int)
-- test1
truncate table 源数据insert into 源数据
select 'k1',1 union all
select 'k1',1
with t as
(select kind,number,
row_number() over(order by getdate()) 'rn' from 源数据),
u as
(select a.kind,a.number,a.rn,
rtrim(b.s)+'-'+rtrim(c.number) 'bh'
from t a
left join
(select kind,sum(number) 's'
from 源数据 group by kind) b on a.kind=b.kind
outer apply
(select isnull((select sum(e.number) from t e
where e.kind=a.kind and e.rn<a.rn),0)+d.number 'number'
from master.dbo.spt_values d
where d.type='P' and d.number between 1 and a.number) c
)
select a.kind,a.number,
stuff((select ','+b.bh from u b
where b.kind=a.kind and b.number=a.number and b.rn=a.rn
for xml path('')),1,1,'') '生成编号'
from u a
group by a.kind,a.number,a.rn
/*
kind number 生成编号
---------- ----------- --------------
k1 1 2-1
k1 1 2-2(2 row(s) affected)
*/
-- test2
truncate table 源数据insert into 源数据
select 'k1',1 union all
select 'k1',2 union all
select 'k2',2
with t as
(select kind,number,
row_number() over(order by getdate()) 'rn' from 源数据),
u as
(select a.kind,a.number,a.rn,
rtrim(b.s)+'-'+rtrim(c.number) 'bh'
from t a
left join
(select kind,sum(number) 's'
from 源数据 group by kind) b on a.kind=b.kind
outer apply
(select isnull((select sum(e.number) from t e
where e.kind=a.kind and e.rn<a.rn),0)+d.number 'number'
from master.dbo.spt_values d
where d.type='P' and d.number between 1 and a.number) c
)
select a.kind,a.number,
stuff((select ','+b.bh from u b
where b.kind=a.kind and b.number=a.number and b.rn=a.rn
for xml path('')),1,1,'') '生成编号'
from u a
group by a.kind,a.number,a.rn
/*
kind number 生成编号
---------- ----------- ----------------
k1 1 3-1
k1 2 3-2,3-3
k2 2 2-1,2-2(3 row(s) affected)
*/
小数,有没有可能成功?