关于期初期末循环累加的SQL

最新推荐文章于 2023-06-15 13:58:09 发布

buynider

最新推荐文章于 2023-06-15 13:58:09 发布

阅读量4.3k

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL 文章标签： insert null table sql object c

本文链接：https://blog.youkuaiyun.com/buynider/article/details/6366867

SQL 专栏收录该内容

36 篇文章

订阅专栏

本文通过创建和填充一个名为Jxc的表来演示SQL中的循环思想。首先检查表是否存在并创建，接着插入初始化数据，最后使用复杂的SQL查询来计算每个ID的期初余额、进货、出货等统计数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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