查询两个表里的数据。 谢谢高手！

Question

DECLARE @a table(日期 smalldatetime,单位  varchar(20), 数量 float)
INSERT @a SELECT '2008-1-1', '甲', 100.0 
union all select '2008-1-15', '乙', 500.0 
union all select '2008-5-5', '甲', 200.0 
declare @b table(日期 smalldatetime,单位 varchar(20), 数量 float)
insert @b select '2008-1-5', 'A', 99.0 
union all select '2008-2-2', 'B', 498.0 
union all select '2008-6-2', 'B', 198.0 
SELECT 日期,单位,收入,发出,结存=(select sum(收入+发出) from 
(
SELECT 日期,单位,数量 收入,0 发出,0 结存 from @a 
UNION ALL
SELECT 日期,单位,0 收入,-数量 发出,0 结存 from @b
)aa WHERE 日期<=bb.日期) from
(
SELECT 日期,单位,数量 收入,0 发出,0 结存 from @a 
UNION ALL
SELECT 日期,单位,0 收入,-数量 发出,0 结存 from @b
)bb ORDER BY 日期--result
/*期                             单位                   收入                             发出                             结存                             
------------------------------ -------------------- ------------------------------ ------------------------------ ------------------------------ 
2008-01-01 00:00:00            甲                                             100.0                            0.0                          100.0 
2008-01-05 00:00:00            A                                               0.0                          -99.0                            1.0 
2008-01-15 00:00:00            乙                                             500.0                            0.0                          501.0 
2008-02-02 00:00:00            B                                               0.0                         -498.0                            3.0 
2008-05-05 00:00:00            甲                                             200.0                            0.0                          203.0 
2008-06-02 00:00:00            B                                               0.0                         -198.0                            5.0 （所影响的行数为 6 行）*/

Answer 1

create table tb1(日期 datetime,单位  varchar(10), 数量 float)
INSERT tb1 SELECT '2008-1-1', '甲', 100.0 
union all select '2008-1-15', '乙', 500.0 
union all select '2008-5-5', '甲', 200.0 
create table tb2(日期 datetime,单位 varchar(10), 数量 float)
insert tb2 select '2008-1-5', 'A', 99.0 
union all select '2008-2-2', 'B', 498.0 
union all select '2008-6-2', 'B', 198.0 select * , 结存 = (select sum(收入 - 发出) from 
(
  select isnull(tb1.日期,tb2.日期) 日期 , isnull(tb1.单位,'') 供应单位 , isnull(tb2.单位,'') 领用单位 , isnull(tb1.数量,0) 收入 , isnull(tb2.数量,0) 发出 from tb1 full join tb2 on tb1.日期 = tb2.日期
) n where 日期 <= m.日期) from
(
  select isnull(tb1.日期,tb2.日期) 日期 , isnull(tb1.单位,'') 供应单位 , isnull(tb2.单位,'') 领用单位 , isnull(tb1.数量,0) 收入 , isnull(tb2.数量,0) 发出 from tb1 full join tb2 on tb1.日期 = tb2.日期
) m
order by 日期drop table tb1 , tb2/*
日期                                                     供应单位       领用单位       收入                                                    发出                                                    结存                                                    
------------------------------------------------------ ---------- ---------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- 
2008-01-01 00:00:00.000                                甲                     100.0                                                 0.0                                                   100.0
2008-01-05 00:00:00.000                                           A          0.0                                                   99.0                                                  1.0
2008-01-15 00:00:00.000                                乙                     500.0                                                 0.0                                                   501.0
2008-02-02 00:00:00.000                                           B          0.0                                                   498.0                                                 3.0
2008-05-05 00:00:00.000                                甲                     200.0                                                 0.0                                                   203.0
2008-06-02 00:00:00.000                                           B          0.0                                                   198.0                                                 5.0（所影响的行数为 6 行）
*/

Answer 2

DECLARE @a table(日期 smalldatetime,单位  varchar(20), 数量 float)
INSERT @a SELECT '2008-1-1', '甲', 100.0 
union all select '2008-1-15', '乙', 500.0 
union all select '2008-5-5', '甲', 200.0 
declare @b table(日期 smalldatetime,单位 varchar(20), 数量 float)
insert @b select '2008-1-5', 'A', 99.0 
union all select '2008-2-2', 'B', 498.0 
union all select '2008-6-2', 'B', 198.0 select 日期  ,单位 as 供应单位 ,'       ' as 领用单位, 数量 as 收入,0 as 发出
into #
from @a
union all
select 日期  ,'       ' as 供应单位 ,单位 as 领用单位, 0 as 收入,数量 as 发出
from @b
order by 1
select * ,
结存 = (select sum(收入 - 发出) from # where 日期 <= m.日期)
from # mdrop table #/*
日期                      供应单位                 领用单位                 收入                     发出                     结存
----------------------- -------------------- -------------------- ---------------------- ---------------------- ----------------------
2008-01-01 00:00:00     甲                                         100                    0                      100
2008-01-05 00:00:00                          A                    0                      99                     1
2008-01-15 00:00:00     乙                                         500                    0                      501
2008-02-02 00:00:00                          B                    0                      498                    3
2008-05-05 00:00:00     甲                                         200                    0                      203
2008-06-02 00:00:00                          B                    0                      198                    5(6 行受影响)
*/