select nian,yue,ri,xingming,qty=min(qty) from T group by nian,yue,ri,xingming
DECLARE @a TABLE(nian int,yue int,ri int,xingming varchar(20),qty decimal(20,2)) INSERT @a SELECT 2008,6,1,'ss',2 UNION ALL SELECT 2008,6,2,'ss',1.5 UNION ALL SELECT 2008,6,3,'ss',4 UNION ALL SELECT 2008,6,4,'ss',3 UNION ALL SELECT 2008,6,1,'rr',5 UNION ALL SELECT 2008,6,2,'rr',2 UNION ALL SELECT 2008,6,3,'rr',2 SELECT * FROM @a UNION ALL SELECT nian,yue,ri,xingming,(SELECT sum(qty) FROM @a WHERE nian=a.nian AND yue=a.yue AND ri<=a.ri AND xingming=a.xingming) FROM @a a ORDER BY nian,yue,xingming,ri,qty
-->生成测试数据
if object_id(N'tempdb..#') is not null drop table # go declare @tb table([nian] nvarchar(4),[yue] int,[ri] int,[xingming] nvarchar(2),[qty] decimal(18,1)) Insert @tb select '2008',6,1,N'ss',2 union all select '2008',6,2,N'ss',1.5 union all select '2008',6,3,N'ss',4 union all select '2008',6,4,N'ss',3 union all select '2008',6,1,N'rr',5 union all select '2008',6,2,N'rr',2 union all select '2008',6,3,N'rr',2 select identity(int,1,1) id ,* into # from @tbselect * from ( select * from @tb union all Select [nian],[yue],[ri],[xingming],(select sum([qty]) from # where id <= t.id ) as [qty] from # t ) a order by 1,2,4 desc,3,5 /* nian yue ri xingming qty ---- ----------- ----------- -------- --------------------------------------- 2008 6 1 ss 2.0 2008 6 1 ss 2.0 2008 6 2 ss 1.5 2008 6 2 ss 3.5 2008 6 3 ss 4.0 2008 6 3 ss 7.5 2008 6 4 ss 3.0 2008 6 4 ss 10.5 2008 6 1 rr 5.0 2008 6 1 rr 15.5 2008 6 2 rr 2.0 2008 6 2 rr 17.5 2008 6 3 rr 2.0 2008 6 3 rr 19.5 */
這樣?黃色字顯示
declare @T table([nian] Datetime,[yue] int,[ri] int,[xingming] nvarchar(2),[qty] decimal(18,1)) Insert @T select '2008',6,1,N'ss',2 union all select '2008',6,2,N'ss',1.5 union all select '2008',6,3,N'ss',4 union all select '2008',6,4,N'ss',3 union all select '2008',6,1,N'rr',5 union all select '2008',6,2,N'rr',2 union all select '2008',6,3,N'rr',2
Select nian,yue,ri,xingming,[qty]=(select sum(Qty) from @T where [nian]=t.[nian] and [yue]=t.[yue] and [xingming]=t.[xingming] and [ri]<=t.[ri]) from @T T nian yue ri xingming qty ----------------------- ----------- ----------- -------- --------------------------------------- 2008-01-01 00:00:00.000 6 1 ss 2.0 2008-01-01 00:00:00.000 6 2 ss 3.5 2008-01-01 00:00:00.000 6 3 ss 7.5 2008-01-01 00:00:00.000 6 4 ss 10.5 2008-01-01 00:00:00.000 6 1 rr 5.0 2008-01-01 00:00:00.000 6 2 rr 7.0 2008-01-01 00:00:00.000 6 3 rr 9.0
INSERT @a SELECT 2008,6,1,'ss',2
UNION ALL SELECT 2008,6,2,'ss',1.5
UNION ALL SELECT 2008,6,3,'ss',4
UNION ALL SELECT 2008,6,4,'ss',3
UNION ALL SELECT 2008,6,1,'rr',5
UNION ALL SELECT 2008,6,2,'rr',2
UNION ALL SELECT 2008,6,3,'rr',2 SELECT * FROM @a
UNION ALL
SELECT nian,yue,ri,xingming,(SELECT sum(qty) FROM @a WHERE nian=a.nian AND yue=a.yue AND ri<=a.ri AND xingming=a.xingming) FROM @a a
ORDER BY nian,yue,xingming,ri,qty
if object_id(N'tempdb..#') is not null
drop table #
go
declare @tb table([nian] nvarchar(4),[yue] int,[ri] int,[xingming] nvarchar(2),[qty] decimal(18,1))
Insert @tb
select '2008',6,1,N'ss',2 union all
select '2008',6,2,N'ss',1.5 union all
select '2008',6,3,N'ss',4 union all
select '2008',6,4,N'ss',3 union all
select '2008',6,1,N'rr',5 union all
select '2008',6,2,N'rr',2 union all
select '2008',6,3,N'rr',2
select identity(int,1,1) id ,* into # from @tbselect * from
(
select * from @tb
union all
Select [nian],[yue],[ri],[xingming],(select sum([qty]) from # where id <= t.id ) as [qty] from # t
) a
order by 1,2,4 desc,3,5
/*
nian yue ri xingming qty
---- ----------- ----------- -------- ---------------------------------------
2008 6 1 ss 2.0
2008 6 1 ss 2.0
2008 6 2 ss 1.5
2008 6 2 ss 3.5
2008 6 3 ss 4.0
2008 6 3 ss 7.5
2008 6 4 ss 3.0
2008 6 4 ss 10.5
2008 6 1 rr 5.0
2008 6 1 rr 15.5
2008 6 2 rr 2.0
2008 6 2 rr 17.5
2008 6 3 rr 2.0
2008 6 3 rr 19.5
*/
declare @T table([nian] Datetime,[yue] int,[ri] int,[xingming] nvarchar(2),[qty] decimal(18,1))
Insert @T
select '2008',6,1,N'ss',2 union all
select '2008',6,2,N'ss',1.5 union all
select '2008',6,3,N'ss',4 union all
select '2008',6,4,N'ss',3 union all
select '2008',6,1,N'rr',5 union all
select '2008',6,2,N'rr',2 union all
select '2008',6,3,N'rr',2
Select nian,yue,ri,xingming,[qty]=(select sum(Qty) from @T where [nian]=t.[nian] and [yue]=t.[yue] and [xingming]=t.[xingming] and [ri]<=t.[ri])
from @T T
nian yue ri xingming qty
----------------------- ----------- ----------- -------- ---------------------------------------
2008-01-01 00:00:00.000 6 1 ss 2.0
2008-01-01 00:00:00.000 6 2 ss 3.5
2008-01-01 00:00:00.000 6 3 ss 7.5
2008-01-01 00:00:00.000 6 4 ss 10.5
2008-01-01 00:00:00.000 6 1 rr 5.0
2008-01-01 00:00:00.000 6 2 rr 7.0
2008-01-01 00:00:00.000 6 3 rr 9.0