日期:2014-05-16 浏览次数:20763 次
declare @t table(ID INT,Name VARCHAR(20),Quantity INT)
insert into @t
select 1,'A' ,10 union all
select 2,'B' ,20 union all
select 3,'B' ,20 union all
select 4,'A' ,10 union all
select 5,'B' ,20 union all
select 6,'A' ,10 union all
select 7,'B' ,20 union all
select 8,'B' ,20 union all
select 9,'A' ,10 union all
select 10,'A',10 union all
select 11,'A',10 union all
select 12,'A',10 union all
select 13,'B',20 union all
select 14,'B',20 union all
select 15,'B',20 union all
select 16,'A',10 union all
select 17,'A',10 union all
select 18,'B',20 union all
select 19,'B',20 union all
select 20,'B',20 union all
select 21,'A',10
--想要得到的结果:
/*
Name Quantity id Lever
---- ----------- -------------- ---------------
A 40 1,4,6,9 Lever1(1~100)
B 60 2,3,5 Lever1(1~100)
A 10 10 Lever2(101~150)
B 40 7,8 Lever2(101~150)
A 50 11,12,16,17,21 Lever3(151~300)
B 100 13,14,15,18,19 Lever3(151~300)
A 0 Lever4(Over301)
B 20 20 Lever4(Over301)
*/
--说明一下:
--根据Name和级别(Lever)分组合计Quantity,
--第一个级别是Quantity等于100,第二个是50,第三个是150,第四个是剩余部分。
----写个简单例子,自己套吧
if object_id('Tempdb..#t') is not null drop table #t
create table #t(
[id] int identity(1,1) not null,
[UserName] nvarchar(10) null
)
Insert Into #t
select 'a' union all
select 'a' union all
select 'c' union all
select 'a' union all
select 'b' union all
select 'c'
select UserName,
stuff((select ','+ cast(id as nvarchar(10)) from #t z where t.UserName=z.UserName for xml path('')), 1, 1, '') IdList<