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,第四个是剩余部分。
------解决方案--------------------
什么版本的数据库？如果是2005和2005以上的，可以用xml的方法，2000的话，只能用循环
------解决方案--------------------
----写个简单例子，自己套吧
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
from #t t
group by UserName
----------------------------
---结果
(6 行受影响)
UserName IdList
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
a 1,2,4
b 5
c 3,6
(3 行受影响)