关于期初期末循环累加的SQL_sql 期初期末-CSDN博客

本文链接：https://blog.csdn.net/buynider/article/details/6366867

if exists (select * from sysobjects where id = object_id(N'Jxc') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
drop table Jxc
end

CREATE TABLE [jxc] (
[fdDate] [datetime] NULL ,
[fdId] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[fdNumber] [decimal](18, 2) NULL ,
[fdCz] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

Insert Jxc (fdDate,fdId,fdNumber,fdCz) Values ( '2011-04-26 00:00:00.000','001',100.00,'期初')
Insert Jxc (fdDate,fdId,fdNumber,fdCz) Values ( '2011-04-26 00:00:00.000','002',200.00,'期初')
Insert Jxc (fdDate,fdId,fdNumber,fdCz) Values ( '2011-04-26 00:00:00.000','001',2000.00,'进货')
Insert Jxc (fdDate,fdId,fdNumber,fdCz) Values ( '2011-04-26 00:00:00.000','001',-500.00,'出货')
Insert Jxc (fdDate,fdId,fdNumber,fdCz) Values ( '2011-04-26 00:00:00.000','001',-300.00,'进货退货')
Insert Jxc (fdDate,fdId,fdNumber,fdCz) Values ( '2011-04-26 00:00:00.000','001',200.00,'出货退货')
Insert Jxc (fdDate,fdId,fdNumber,fdCz) Values ( '2011-04-27 00:00:00.000','001',600.00,'进货')
Insert Jxc (fdDate,fdId,fdNumber,fdCz) Values ( '2011-04-27 00:00:00.000','003',100.00,'进货')
select [fdDate] 日期,[fdId] 编号,
     [期初]=isnull((select sum(fdNumber) from jxc
        where [fdId]=a.[fdId] and DATEDIFF(day,[fdDate],a.[fdDate])>=1),
          isnull((select top 1 fdNumber from jxc
        where [fdId]=a.[fdId] and [fdCz]='期初' order by [fdDate]),0)),
       sum(case when [fdCz]='进货' then [fdNumber] else 0 end) [进货],
       sum(case when [fdCz]='出货' then [fdNumber] else 0 end) [出货],
       sum(case when [fdCz]='进货退货' then [fdNumber] else 0 end) [进货退货],
       sum(case when [fdCz]='出货退货' then [fdNumber] else 0 end) [出货退货],
       [期末库存]=(select SUM(fdNumber) from jxc where [fdId]=a.[fdId]
                and [fdDate]<=a.[fdDate])
from jxc a group by [fdDate],[fdId]

摘自　http://topic.csdn.net/u/20110426/14/aaf9ab0b-46c9-499e-8984-3809da2c33a5.html?65602

其实：这条语句体现了SQL中的循环思想，在外围套一个大循环 select * from jxc,在Select *就是循环遍历表中的每一条记录(select top 1 fdNumber from jxc
where [fdId]=a.[fdId] and [fdCz]='期初' order by [fdDate]),0)这句话是在遍历表jxc中的每一条记录的同时，查找fdid值相同，并且是期初的值,这里体现了循环的思想