显示结果
入库 出存 （这个是我希望得出的）
10 0 10
10 3 17
第一行的 最后的数是 入库-出库,得出的一个新列数据
第二行的新列数据是(第一行的新列+第二行入库-出库)
-------------------
这是我的代码
select a.id,a.indate,a.item,sum(a.qty) as inqty,sum(outqty) as outqty,sum(a.qty-outqty) as zh from
(
(select a.id,a.item,b.indate,a.qty,outqty=0,tag='A'
from in2 a left join in1 b on a.id=b.id where a.item='联塑')
union all
(select c.id,c.item,d.outdate as indate,qty=0,c.qty as outqty,tag='B'
from out2 c left join out1 d on c.id=d.id where c.item='联塑')
) a
group by a.id,a.item,a.indate,a.tag
order by a.tag
---------------下面结果
100703005 2010-07-01 00:00:00.000 联塑 15.00 .00 15.00
100706001 2010-07-03 00:00:00.000 联塑 .00 15.00 -15.00
----------------------
我得出的结果是-15的,但这不是我想要的.我要的是
结果为0.
==========大侠们帮我修改下吧
10 个解决方案
#1
create table tb(inum int,tnum int)
insert into tb
select 10,0 union all
select 10,3 union all
select 2,4 union all
select 4,10
go
select *,id = identity(int,1,1)
into #t
from tb
select *,(select sum(isnull(inum,0) - isnull(tnum,0)) from #t where id <= t.id)
from #t t
drop table tb,#t
/*
inum tnum id
----------- ----------- ----------- -----------
10 0 1 10
10 3 2 17
2 4 3 15
4 10 4 9
(4 行受影响)
#2
use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
入库 int,
出库 int
)
go
--插入测试数据
insert into tb select 10,0
union all select 10,3
go
--代码实现
;with cte as(select idd=row_number()over(order by getdate()),*,num=入库-出库 from tb)
select 入库,出库,num=(select sum(num) from cte where idd<=t.idd)
from cte t
/*测试结果
入库 出库 num
---------------------
10 0 10
10 3 17
(2 行受影响)
*/
#3
。。不懂！！
怎么加在我的代码上？
怎么加在我的代码上？
select a.id,a.indate,a.item,sum(a.qty) as inqty,sum(outqty) as outqty,sum(a.qty-outqty) as zh from
(
(select a.id,a.item,b.indate,a.qty,outqty=0,tag='A'
from in2 a left join in1 b on a.id=b.id where a.item='联塑')
union all
(select c.id,c.item,d.outdate as indate,qty=0,c.qty as outqty,tag='B'
from out2 c left join out1 d on c.id=d.id where c.item='联塑')
) a
group by a.id,a.item,a.indate,a.tag
order by a.tag
#4
我的代码的这个sum(a.qty-outqty)
是结果，但是怎么把这个配合你们的代码实现呢？
是结果，但是怎么把这个配合你们的代码实现呢？
#5
求答案！
#6
把表结构和测试数据贴出来啊，光看语句太晕
#7
--上面的查询保存到一个临时表，再用以下update 一下
update 临时表 set zh=(select sum(zh) from 临时表 a where a.item=临时表.item and a.indate<=tb.indate),
--期初的０.00应该是15也改一下
inqty=(select sum(zh) from 临时表 a
where a.item=临时表.item and a.indate<=tb.indate)-临时表.outqty
#8
select a.id,a.indate,a.item,sum(a.qty) as inqty,sum(outqty) as outqty,sum(a.qty-outqty) as zh from
(
(select a.id,a.item,b.indate,a.qty,outqty=0,tag='A'
from in2 a left join in1 b on a.id=b.id where a.item='电脑机.瑞士机.6机头单钩')
union all
(select c.id,c.item,d.outdate as indate,qty=0,c.qty as outqty,tag='B'
from out2 c left join out1 d on c.id=d.id where c.item='电脑机.瑞士机.6机头单钩')
) a
group by a.id,a.item,a.indate,a.tag
order by a.tag
#9
大家看到了吧.每一行的ZH 的结果是 INQTY-OUTQTY 所得出来的,
这样第一行结果是对的,
但是第二行的ZH 我需要加上第一行的ZH.
第三行的+第二行的.一直这样循环下去
这样第一行结果是对的,
但是第二行的ZH 我需要加上第一行的ZH.
第三行的+第二行的.一直这样循环下去
#10
请问怎么建立临时表
#1
create table tb(inum int,tnum int)
insert into tb
select 10,0 union all
select 10,3 union all
select 2,4 union all
select 4,10
go
select *,id = identity(int,1,1)
into #t
from tb
select *,(select sum(isnull(inum,0) - isnull(tnum,0)) from #t where id <= t.id)
from #t t
drop table tb,#t
/*
inum tnum id
----------- ----------- ----------- -----------
10 0 1 10
10 3 2 17
2 4 3 15
4 10 4 9
(4 行受影响)
#2
use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
入库 int,
出库 int
)
go
--插入测试数据
insert into tb select 10,0
union all select 10,3
go
--代码实现
;with cte as(select idd=row_number()over(order by getdate()),*,num=入库-出库 from tb)
select 入库,出库,num=(select sum(num) from cte where idd<=t.idd)
from cte t
/*测试结果
入库 出库 num
---------------------
10 0 10
10 3 17
(2 行受影响)
*/
#3
。。不懂！！
怎么加在我的代码上？
怎么加在我的代码上？
select a.id,a.indate,a.item,sum(a.qty) as inqty,sum(outqty) as outqty,sum(a.qty-outqty) as zh from
(
(select a.id,a.item,b.indate,a.qty,outqty=0,tag='A'
from in2 a left join in1 b on a.id=b.id where a.item='联塑')
union all
(select c.id,c.item,d.outdate as indate,qty=0,c.qty as outqty,tag='B'
from out2 c left join out1 d on c.id=d.id where c.item='联塑')
) a
group by a.id,a.item,a.indate,a.tag
order by a.tag
#4
我的代码的这个sum(a.qty-outqty)
是结果，但是怎么把这个配合你们的代码实现呢？
是结果，但是怎么把这个配合你们的代码实现呢？
#5
求答案！
#6
把表结构和测试数据贴出来啊，光看语句太晕
#7
--上面的查询保存到一个临时表，再用以下update 一下
update 临时表 set zh=(select sum(zh) from 临时表 a where a.item=临时表.item and a.indate<=tb.indate),
--期初的０.00应该是15也改一下
inqty=(select sum(zh) from 临时表 a
where a.item=临时表.item and a.indate<=tb.indate)-临时表.outqty
#8
select a.id,a.indate,a.item,sum(a.qty) as inqty,sum(outqty) as outqty,sum(a.qty-outqty) as zh from
(
(select a.id,a.item,b.indate,a.qty,outqty=0,tag='A'
from in2 a left join in1 b on a.id=b.id where a.item='电脑机.瑞士机.6机头单钩')
union all
(select c.id,c.item,d.outdate as indate,qty=0,c.qty as outqty,tag='B'
from out2 c left join out1 d on c.id=d.id where c.item='电脑机.瑞士机.6机头单钩')
) a
group by a.id,a.item,a.indate,a.tag
order by a.tag
#9
大家看到了吧.每一行的ZH 的结果是 INQTY-OUTQTY 所得出来的,
这样第一行结果是对的,
但是第二行的ZH 我需要加上第一行的ZH.
第三行的+第二行的.一直这样循环下去
这样第一行结果是对的,
但是第二行的ZH 我需要加上第一行的ZH.
第三行的+第二行的.一直这样循环下去
#10
请问怎么建立临时表