22,210
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2012-06-14 17:12:45
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([line] int,[count] int,[serial] int)
insert [tb]
select 1,2,22 union all
select 1,3,23 union all
select 2,7,24
--------------开始查询--------------------------
;with f as
(
select line,serial,sum([count]) as num from tb group by line,serial
)
select
a.line,c.number,b.serial
from
f a join (select distinct line,serial from tb)b
on
a.line=b.line
and
a.serial=b.serial
left join
master..spt_values c
on
c.number<=a.num
and
c.number>0
and
c.[type]='p'
----------------结果----------------------------
/* line number serial
----------- ----------- -----------
1 1 22
1 2 22
1 1 23
1 2 23
1 3 23
2 1 24
2 2 24
2 3 24
2 4 24
2 5 24
2 6 24
2 7 24
(12 行受影响)
*/