表1 table1
入库编号 单价 数量 金额 时间
2009001 10 5 50 2009-1-4
表2 table2
出库编号 单价 数量 金额 时间
2009010 10 2 20 2009-5-4生成如下样式
时间 编号 借方单价 借方数量 借方金额 贷方单价 贷方数量 贷方金额 余量 余额
2009-1-4 2009001 10 5 50 5 50
2009-5-4 2009010 10 2 20 3 30
入库编号 单价 数量 金额 时间
2009001 10 5 50 2009-1-4
表2 table2
出库编号 单价 数量 金额 时间
2009010 10 2 20 2009-5-4生成如下样式
时间 编号 借方单价 借方数量 借方金额 贷方单价 贷方数量 贷方金额 余量 余额
2009-1-4 2009001 10 5 50 5 50
2009-5-4 2009010 10 2 20 3 30
as
(select *,Type=1 from t1
union all
select *,Type=2 from t2)
,Cte2
as
(select *,row=row_number()over(order by 时间),
余额=(select sum(case when type=1 then 数量 else -数量 end) from Cte where 时间<=a.时间)
from Cte a)
select
时间,
sum(case when Type=1 then 数量 else 0 end) as.......
.......
from
CTE2
group by 时间
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb1') IS NOT NULL
DROP TABLE tb1
GO
CREATE TABLE tb1(入库编号 varchar(10),单价 int,数量 int,金额 int,时间 datetime)
go
insert tb1 SELECT
'2009001', 10 , 5 , 50, '2009-1-4'
go
IF OBJECT_ID('tb2') IS NOT NULL
DROP TABLE tb2
GO
CREATE TABLE tb2(出库编号 varchar(10),单价 int,数量 int,金额 int,时间 datetime)
go
insert tb2 SELECT
'2009010', 10 , 2 , 20 , '2009-5-4'
go
select 时间=ISNULL(a.时间,b.时间),
编号=isnull(a.入库编号,b.出库编号 ),
借方单价= a.单价 ,
借方数量=a.数量,
借方金额=a.金额,
贷方单价=b.单价,
贷方数量=b.数量,
贷方金额=b.金额 into #
from tb1 a full join tb2 b on a.入库编号=b.出库编号
select *,
余量=isnull((select SUM(数量) from tb1 where 时间<=#.时间),0)-isnull((select SUM(数量) from tb2 where 时间<=#.时间),0),
余额=isnull((select SUM(金额) from tb1 where 时间<=#.时间),0)-isnull((select SUM(金额) from tb2 where 时间<=#.时间),0)
from #
go
/*------------
时间 编号 借方单价 借方数量 借方金额 贷方单价 贷方数量 贷方金额 余量 余额
----------------------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2009-01-04 00:00:00.000 2009001 10 5 50 NULL NULL NULL 5 50
2009-05-04 00:00:00.000 2009010 NULL NULL NULL 10 2 20 3 30(2 行受影响)
-------*/
from ((select 时间,入库编号 as 编号,单价 as 借方单价,数量 as 借方数量,金额 as 借方金额,0 as 贷方单价,0 as 贷方数量 ,0 as 贷方金额 from table1)
union all
(select 时间,出库编号 as 编号,0 as 借方单价,0 as 借方数量,0 as 借方金额,单价 as 贷方单价,数量 as 贷方数量 ,金额 as 贷方金额 from table2)) table3
order by table3.时间
;
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb1') IS NOT NULL
DROP TABLE tb1
GO
CREATE TABLE tb1(入库编号 varchar(10),单价 int,数量 int,金额 int,时间 datetime)
go
insert tb1 SELECT
'2009001', 10 , 5 , 50, '2009-1-4'
go
IF OBJECT_ID('tb2') IS NOT NULL
DROP TABLE tb2
GO
CREATE TABLE tb2(出库编号 varchar(10),单价 int,数量 int,金额 int,时间 datetime)
go
insert tb2 SELECT
'2009010', 10 , 2 , 20 , '2009-5-4'
go
select 时间=convert(varchar(10),ISNULL(a.时间,b.时间),120),
编号=isnull(a.入库编号,b.出库编号 ),
借方单价= a.单价 ,
借方数量=a.数量,
借方金额=a.金额,
贷方单价=b.单价,
贷方数量=b.数量,
贷方金额=b.金额
into #
from tb1 a full join tb2 b on a.入库编号=b.出库编号
select *,
余量=isnull((select SUM(数量) from tb1 where 时间<=#.时间),0)-isnull((select SUM(数量) from tb2 where 时间<=#.时间),0),
余额=isnull((select SUM(金额) from tb1 where 时间<=#.时间),0)-isnull((select SUM(金额) from tb2 where 时间<=#.时间),0)
from #
go
/*------------
时间 编号 借方单价 借方数量 借方金额 贷方单价 贷方数量 贷方金额 余量 余额
---------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2009-01-04 2009001 10 5 50 NULL NULL NULL 5 50
2009-05-04 2009010 NULL NULL NULL 10 2 20 3 30(2 行受影响)(2 行受影响)
-------*/
if not object_id('Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([入库编号] int,[单价] int,[数量] int,[金额] int,[时间] Datetime)
Insert #T1
select 2009001,10,5,50,'2009-1-4'
Go
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([出库编号] int,[单价] int,[数量] int,[金额] int,[时间] Datetime)
Insert #T2
select 2009010,10,2,20,'2009-5-4'
Goif not object_id('Tempdb..#') is null
drop table #
select
*,row=identity(int,1,1)
into #
from
(select *,Type=1 from #T1
union all
select *,Type=2 from #T2
)T order by [时间]select
a.时间,
a.[入库编号] as 编号,
max(case when Type=1 then rtrim(a.[数量]) else '' end) as 借方数量,
max(case when Type=1 then rtrim(a.[单价]) else '' end) as 借方单价,
max(case when Type=1 then rtrim(a.[金额]) else '' end) as 借方金额,
max(case when Type=2 then rtrim(a.[数量]) else '' end) as 贷方数量,
max(case when Type=2 then rtrim(a.[单价]) else '' end) as 贷方单价,
max(case when Type=2 then rtrim(a.[金额]) else '' end) as 贷方金额,
[余量],[余额]
from
(select
a.时间,a.Type,a.[数量],a.[单价],a.[金额],a.[入库编号],
[余量]=sum(case when b.Type=1 then b.[数量] else -b.[数量] end),
[余额]=sum(case when b.Type=1 then b.[数量]* b.[单价] else -b.[数量]*b.[单价] end)
from # a,# b
where a.row>=b.row
group by a.row,a.时间,a.Type,a.[数量],a.[单价],a.[金额],a.[入库编号]) as a
group by a.时间,a.[入库编号],[余量],[余额]
(1 行受影响)(1 行受影响)(2 行受影响)
时间 编号 借方数量 借方单价 借方金额 贷方数量 贷方单价 贷方金额 余量 余额
----------------------- ----------- ------------ ------------ ------------ ------------ ------------ ------------ ----------- -----------
2009-01-04 00:00:00.000 2009001 5 10 50 5 50
2009-05-04 00:00:00.000 2009010 2 10 20 3 30(2 行受影响)
a.时间,
a.[入库编号] as 编号,
max(case when Type=1 then rtrim(a.[数量]) else '' end) as 借方数量,
max(case when Type=1 then rtrim(a.[单价]) else '' end) as 借方单价,
max(case when Type=1 then rtrim(a.[金额]) else '' end) as 借方金额,
max(case when Type=2 then rtrim(a.[数量]) else '' end) as 贷方数量,
max(case when Type=2 then rtrim(a.[单价]) else '' end) as 贷方单价,
max(case when Type=2 then rtrim(a.[金额]) else '' end) as 贷方金额,
[余量],[余额]
from
(select
a.时间,a.Type,a.[数量],a.[单价],a.[金额],a.[入库编号],
[余量]=sum(case when b.Type=1 then b.[数量] else -b.[数量] end),
[余额]=sum(case when b.Type=1 then b.[金额] else -b.[金额] end)--这里取金额
from # a,# b
where a.row>=b.row
group by a.row,a.时间,a.Type,a.[数量],a.[单价],a.[金额],a.[入库编号]) as a
group by a.时间,a.[入库编号],[余量],[余额]
select 时间=ISNULL(a.时间,b.时间),
编号=isnull(a.入库编号,b.出库编号 ),
借方单价= a.单价 ,
借方数量=a.数量,
借方金额=a.金额,
贷方单价=b.单价,
贷方数量=b.数量,
贷方金额=b.金额 into #
from tb1 a full join tb2 b on a.入库编号=b.出库编号
select *,
余量=isnull((select SUM(数量) from tb1 where 时间<=#.时间),0)-isnull((select SUM(数量) from tb2 where 时间<=#.时间),0),
余额=isnull((select SUM(金额) from tb1 where 时间<=#.时间),0)-isnull((select SUM(金额) from tb2 where 时间<=#.时间),0)
from # 里面的#就是那个临时表吗?
你插入临时表后可以先看看
select * from # 这里有什么
接着看下面的句子 就好理解了。。
select [a.type],[a.class] from [member] a