数据处理
原始数据
id(IX)qty
0015
0015
0012
0015
0011
0036
0036
0031
0031
0036
0036
0036
0054
0054
0054
0053
0053
0053
变成下列的统计数据
idmemo
0015X3+2X1+1X1
0036X5+1X2
0054X3+3X3
行业数据
[解决办法]
if exists(select * from sysobjects where name= 'st')
drop table st
go
create table st
(
id nvarchar(10),
qty int
)
go
insert into st
select '001',5 union all
select '001',5 union all
select '001',5 union all
select '001',2 union all
select '001',2 union all
select '002',4 union all
select '002',4 union all
select '002',6 union all
select '002',6
go
select id,
stuff((select '+'+convert(nvarchar(10),qty)+'*'+convert(nvarchar(10),count(qty)) from st st1
where st1.id = st2.id group by id,qty for xml path('')),1,1,'')d
from st st2
group by id
create table yon
(id varchar(5),qty int)
insert into yon
select '001', 5 union all
select '001', 5 union all
select '001', 2 union all
select '001', 5 union all
select '001', 1 union all
select '003', 6 union all
select '003', 6 union all
select '003', 1 union all
select '003', 1 union all
select '003', 6 union all
select '003', 6 union all
select '003', 6 union all
select '005', 4 union all
select '005', 4 union all
select '005', 4 union all
select '005', 3 union all
select '005', 3 union all
select '005', 3
with t as
(select id,rtrim(qty)+'X'+rtrim(count(1)) 's',
row_number() over(partition by id order by getdate()) 'rn'
from yon group by id,qty
)
select a.id,
stuff((select '+'+b.s
from t b
where b.id=a.id
order by b.rn desc
for xml path('')),1,1,'') 'memo'
from t a
group by a.id
/*
id memo
----- --------------------
001 5X3+2X1+1X1
003 6X5+1X2
005 4X3+3X3
(3 row(s) affected)
*/
