首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

初始期数,月结,库存解决办法

2012-04-15 
初始期数,月结,库存原表期初收发期末2010/7/18 12570012570.5mmx8-3/4吋x不锈钢卷片2010/9/800125700.5mmx

初始期数,月结,库存
原表
期初收发期末
2010/7/18 12570012570.5mmx8-3/4吋x不锈钢卷片
2010/9/800 125700.5mmx8-3/4吋x不锈钢卷片
2010/9/280 3694036940.5mmx8-3/4吋x不锈钢卷片
2010/10/190094927450.5mmx8-3/4吋x不锈钢卷片
2010/10/230085018950.5mmx8-3/4吋x不锈钢卷片
2010/11/100 09439520.5mmx8-3/4吋x不锈钢卷片
我想要的是 这样。。
期初收发期末
2010/7/18 12570012570.5mmx8-3/4吋x不锈钢卷片
2010/9/812570 125700.5mmx8-3/4吋x不锈钢卷片
2010/9/280 3694036940.5mmx8-3/4吋x不锈钢卷片
2010/10/193697094927450.5mmx8-3/4吋x不锈钢卷片
2010/10/230085018950.5mmx8-3/4吋x不锈钢卷片
2010/11/101895 09439520.5mmx8-3/4吋x不锈钢卷片


2010/7/18 12570012570.5mmx8-3/4吋x不锈钢卷片
2010/9/112570 125712570.5mmx8-3/4吋x不锈钢卷片2010/9/800 125700.5mmx8-3/4吋x不锈钢卷片
2010/9/280 3694036940.5mmx8-3/4吋x不锈钢卷片
2010/10/1936940036940.5mmx8-3/4吋x不锈钢卷片
2010/10/190094927450.5mmx8-3/4吋x不锈钢卷片
2010/10/230085018950.5mmx8-3/4吋x不锈钢卷片
2010/11/011895 0018950.5mmx8-3/4吋x不锈钢卷片2010/11/100 09439520.5mmx8-3/4吋x不锈钢卷片

[解决办法]

SQL code
CREATE TABLE [dbo].[test](    [id] [int] IDENTITY(1,1) NOT NULL,    [riqi] [smalldatetime] NULL,    [qichu] [int] NULL,    [shou] [int] NULL,    [fa] [int] NULL,    [qimo] [int] NULL,    [title] [varchar](50) NULL, CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED (    [id] ASC)) ON [PRIMARY]--测试数据INSERT INTO test(riqi, qichu, shou, fa, qimo, title)VALUES (CONVERT(DATETIME, '2010/7/18', 102), 1257, 0, 0, 1257, '0.5mmx8-3/4吋x不锈钢卷片')INSERT INTO test(riqi, qichu, shou, fa, qimo, title)VALUES (CONVERT(DATETIME, '2010/9/8', 102), 0, 0, 1257, 0, '0.5mmx8-3/4吋x不锈钢卷片')INSERT INTO test(riqi, qichu, shou, fa, qimo, title)VALUES (CONVERT(DATETIME, '2010/9/28', 102), 0, 3694, 0, 3694, '0.5mmx8-3/4吋x不锈钢卷片')INSERT INTO test(riqi, qichu, shou, fa, qimo, title)VALUES (CONVERT(DATETIME, '2010/10/19', 102), 0, 0, 949, 2745, '0.5mmx8-3/4吋x不锈钢卷片')INSERT INTO test(riqi, qichu, shou, fa, qimo, title)VALUES (CONVERT(DATETIME, '2010/10/23', 102), 1, 0, 850, 1895, '0.5mmx8-3/4吋x不锈钢卷片')INSERT INTO test(riqi, qichu, shou, fa, qimo, title)VALUES (CONVERT(DATETIME, '2010/11/10', 102), 0, 0, 943, 952, '0.5mmx8-3/4吋x不锈钢卷片')--更新每月第一条记录的期初数(即前一次收发的期末数)--如果id排序与riqi排序不一致,可能会导致结果不正确。update [test]set qichu=(select top 1 qimo from [test] t2 where t2.id<test.id order by id desc)where id in(    select id from (    SELECT [id],qimo,          (select COUNT(1) from [test] t1 where t1.riqi<t.riqi and YEAR(t.riqi)=YEAR(t1.riqi) and MONTH(t.riqi)=MONTH(t1.riqi)) as st      FROM [pay].[dbo].[test] t) b where st=0 and id>1  )  --插入月初记录--如果当月无任务记录则无法补充月初记录--找出每月第一条记录,判断是否为月初,不为月初时则返回月初记录值--将月初记录值插入到表中INSERT INTO test(riqi, qichu, shou, fa, qimo, title)select yuechu as riqi,((select top 1 qimo from [test] t2 where t2.riqi<d.yuechu order by id desc)) as qichu,0 as shou,0 as fa, ((select top 1 qimo from [test] t2 where t2.riqi<d.yuechu order by id desc))  as qimo,title from(          select *,(select dateadd(d,-day(riqi)+1,riqi)) as yuechu from test      where id in(        select id from (            SELECT [id],qimo,              (select COUNT(1) from [test] t1 where t1.riqi<t.riqi and YEAR(t.riqi)=YEAR(t1.riqi) and MONTH(t.riqi)=MONTH(t1.riqi)) as st            FROM [pay].[dbo].[test] t) b where st=0 and id>1            )) d where riqi<>yuechu   select * from test order by riqi  id          riqi                    qichu       shou        fa          qimo        title----------- ----------------------- ----------- ----------- ----------- ----------- --------------------------------------------------1           2010-07-18 00:00:00     1257        0           0           1257        0.5mmx8-3/4吋x不锈钢卷片2           2010-09-08 00:00:00     1257        0           1257        0           0.5mmx8-3/4吋x不锈钢卷片3           2010-09-28 00:00:00     0           3694        0           3694        0.5mmx8-3/4吋x不锈钢卷片4           2010-10-19 00:00:00     3694        0           949         2745        0.5mmx8-3/4吋x不锈钢卷片5           2010-10-23 00:00:00     1           0           850         1895        0.5mmx8-3/4吋x不锈钢卷片6           2010-11-01 00:00:00     1895        0           943         952         0.5mmx8-3/4吋x不锈钢卷片7           2010-09-01 00:00:00     1257        0           0           1257        0.5mmx8-3/4吋x不锈钢卷片8           2010-10-01 00:00:00     3694        0           0           3694        0.5mmx8-3/4吋x不锈钢卷片(8 行受影响)  --drop table test 

热点排行