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值相同,并且是期初的值,这里体现了循环的思想