流水明细帐行列转换及计算期初期未数据

Question

本帖最后由 neterr 于 2010-05-16 15:04:25 编辑

Answer 1

大概可以if object_id('tb') is not null drop table tb
go
create table tb(RQ datetime ,LX varchar(10), KHDM varchar(10), JE numeric(10,2))
insert tb
select '2010-03-01', '进货', 'D01', 20.00 union all
select '2010-03-01', '进货', 'D01', 90.00 union all
select '2010-03-01', '出货', 'D01', 70.00 union all
select '2010-03-05', '进货', 'D01', 10.00
with t1 as(
select rowid=row_number()over(partition by KHDM order by RQ),* from tb )
select 日期=a.rq,
期初=(select isnull(sum(case lx when '进货' then je else -je end),0) from t1 where rowid<a.rowid)+
(case lx when '进货' then je else -je end),
进货=(case lx when '进货' then je else 0 end),
出货=(case lx when '进货' then 0 else je end),
本期未=(select isnull(sum(case lx when '进货' then je else -je end),0) from t1 where rowid<=a.rowid)
from t1 a/*日期                                                     期初                                       进货           出货           本期未                                      
------------------------------------------------------ ---------------------------------------- ------------ ------------ ---------------------------------------- 
2010-03-01 00:00:00.000                                20.00                                    20.00        .00          20.00
2010-03-01 00:00:00.000                                110.00                                   90.00        .00          110.00
2010-03-01 00:00:00.000                                40.00                                    .00          70.00        40.00
2010-03-05 00:00:00.000                                50.00                                    10.00        .00          50.00（所影响的行数为 4 行）*/

Answer 2

就算差不多吧if object_id('tb') is not null drop table tb
go
create table tb(RQ datetime ,LX varchar(10), KHDM varchar(10), JE numeric(10,2))
insert tb
select '2010-03-01', '进货', 'D01', 90.00 union all
select '2010-03-01', '出货', 'D01', 70.00 union all
select '2010-03-05', '进货', 'D01', 10.00
if object_id('tc') is not null drop table tc
go
create table tc (KHDM varchar(10), JE numeric(10,2))
insert tc values('D01',50.00)with t1 as(
select 0 as rowid,
min(b.rq) rq,
'进货' as lx,
b.KHDM,
max(c.je)-sum(case b.lx when '进货' then b.je else -b.je end) as je
from tb b, tc c where b.KHDM=c.KHDM group by b.KHDM union all
select rowid=row_number()over(partition by KHDM order by RQ),* from tb )select 日期=a.rq,
期初=(select isnull(sum(case lx when '进货' then je else -je end),0) from t1 where rowid<a.rowid)+
(case lx when '进货' then je else -je end),
进货=(case lx when '进货' then je else 0 end),
出货=(case lx when '进货' then 0 else je end),
本期未=(select isnull(sum(case lx when '进货' then je else -je end),0) from t1 where rowid<=a.rowid)
from t1 a/*
日期                                                     期初                                       进货                                       出货                                       本期未                                      
------------------------------------------------------ ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- 
2010-03-01 00:00:00.000                                20.00                                    20.00                                    .00                                      20.00
2010-03-01 00:00:00.000                                110.00                                   90.00                                    .00                                      110.00
2010-03-01 00:00:00.000                                40.00                                    .00                                      70.00                                    40.00
2010-03-05 00:00:00.000                                50.00                                    10.00                                    .00                                      50.00（所影响的行数为 4 行）
*/

Answer 3

为了好看if object_id('tb') is not null drop table tb
go
create table tb(RQ datetime ,LX varchar(10), KHDM varchar(10), JE numeric(10,2))
insert tb
select '2010-03-01', '进货', 'D01', 90.00 union all
select '2010-03-01', '出货', 'D01', 70.00 union all
select '2010-03-05', '进货', 'D01', 10.00
if object_id('tc') is not null drop table tc
go
create table tc (KHDM varchar(10), JE numeric(10,2))
insert tc values('D01',50.00)with t as
(select rowid=convert(int,row_number()over(partition by KHDM order by RQ)),* from tb)
,t1 as(
select convert(int,0) as rowid,
min(b.rq) as 日期,
convert(numeric(10,2),max(c.je)-sum(case b.lx when '进货' then b.je else -b.je end)) as 期初,
convert(numeric(10,2),0) as 进货,
convert(numeric(10,2),0)  as 出货,
max(c.je)-sum(case b.lx when '进货' then b.je else -b.je end) as 本期未
from tb b, tc c where b.KHDM=c.KHDM group by b.KHDM  union all
select b.rowid,
b.rq,
convert(numeric(10,2),0)  as 期初,
(case b.lx when '进货' then je else 0.0 end) as 进货,
(case b.lx when '进货' then 0.0 else je end) as 出货,
a.本期未+(case b.lx when '进货' then je else -je end) as 本期未
from t b, t1 a 
where b.rowid =a.rowid+1 )select convert(varchar(10),日期,120) 日期,                                                     
case 期初 when 0 then '' else ltrim(期初) end 期初,           
case 进货 when 0 then '' else ltrim(进货) end 进货,           
case 出货 when 0 then '' else ltrim(出货) end 出货,           
case 本期未 when 0 then '' else ltrim(本期未) end 本期未                                    
from t1/*
日期         期初                                        进货                                        出货                                        本期未                                       
---------- ----------------------------------------- ----------------------------------------- ----------------------------------------- ----------------------------------------- 
2010-03-01 20.00                                                                                                                         20.00
2010-03-01                                           90.00                                                                               110.00
2010-03-01                                                                                     70.00                                     40.00
2010-03-05                                           10.00                                                                               50.00（所影响的行数为 4 行）
*/

Answer 4

xys_777 你的用的是mssql2005?我的数据库是mssql2000,不能用 with as，麻烦用sql2000重写一下呢？先谢谢了！

Answer 5

--sql2000的问题是没有生成序号的函数，所以就要使用自增列，于是干脆写个存储过程if object_id('tb') is not null drop table tb
go
create table tb(RQ datetime ,LX varchar(10), KHDM varchar(10), JE numeric(10,2))
insert tb
select '2010-03-01', '进货', 'D01', 90.00 union all
select '2010-03-01', '出货', 'D01', 70.00 union all
select '2010-03-05', '进货', 'D01', 10.00
if object_id('tc') is not null drop table tc
go
create table tc (KHDM varchar(10), JE numeric(10,2))
insert tc values('D01',50.00)
create proc p_test @khdm varchar(20)
as
begin
    select rowid=identity(int,1,1),* into #t  from tb    select convert(int,0) as rowid,
    min(b.rq) as 日期,
    convert(numeric(10,2),max(c.je)-sum(case b.lx when '进货' then b.je else -b.je end)) as 期初,
    convert(numeric(10,2),0) as 进货,
    convert(numeric(10,2),0)  as 出货,
    max(c.je)-sum(case b.lx when '进货' then b.je else -b.je end) as 本期未
    into #t1
    from tb b, tc c where b.KHDM=c.KHDM group by b.KHDM
    
    select convert(varchar(10),日期,120) 日期,                                                     
    case 期初 when 0 then '' else ltrim(期初) end 期初,           
    case 进货 when 0 then '' else ltrim(进货) end 进货,           
    case 出货 when 0 then '' else ltrim(出货) end 出货,           
    case 本期未 when 0 then '' else ltrim(本期未) end 本期未
    from( 
    select rowid,日期,期初,进货,出货,本期未 from #t1 union all
    select b.rowid,
    b.rq,
    convert(numeric(10,2),0)  as 期初,
    (case b.lx when '进货' then b.je else 0.0 end) as 进货,
    (case b.lx when '进货' then 0.0 else b.je end) as 出货,
    a.本期未+sum(isnull((case c.lx when '进货' then c.je else -c.je end),0))  as 本期未
    from #t b left join #t c on b.rowid>=c.rowid,#t1 a
    group by b.rowid,b.rq,b.lx,b.je,a.本期未) t
endexec p_test 'D01'/*
日期         期初                                        进货                                        出货                                        本期未                                       
---------- ----------------------------------------- ----------------------------------------- ----------------------------------------- ----------------------------------------- 
2010-03-01 20.00                                                                                                                         20.00
2010-03-01                                           90.00                                                                               110.00
2010-03-01                                                                                     70.00                                     40.00
2010-03-05                                           10.00                                                                               50.00（所影响的行数为 4 行）*/