如何将三张表中的信息汇总到一张表中（不管用什么方式）

[hdjliyisss]

表一：bianhao chukujine shuliang nianyue chukuhzb
001 200 100 2011-09-09
001 300 200 2011-09-10
002 204 23 2011-09-10
002 234 27 2011-09-11
表二：bianhao kucunjine kucunshuliang nianyue kucunhzb
001 400 300 2011-09-09
001 300 400 2011-09-10
002 323 321 2011-09-10
002 321 434 2011-09-11
表三：bianhao rukujine rukushuliang nianyue rukuhzb
001 21 32 2011-09-08
001 32 32 2011-09-10
002 204 32 2011-09-11
002 232 323 2011-09-11
解释：三张表为chukuhzb，kucunhzb，rukuhzb 分别是出库表，库存表，入库表。库存表=入库-出库。（中数据不是很准确），三张表每次变动都会增加记录。比如，在9-28 出库表中001出库50个，那么001增加记录为001 225 150 2011-09-28
.其他表同理。现在要求把三张表汇总在一起，以编号为基准，分别每条数据最后日期输入：
表样：
bianhao kucunjine kucunshuliang rukujine rukushuliang chukujine chukushuliang
001 300 400 32 32 225 150
002 321 434 232 323 234 27
求求各位大神，快速给分！

[hdjliyisss]

[Quote=引用 1 楼 fredrickhu 的回复:]
没看出来是怎么算出来的 225 和 150
[/Quote]
不需用知道怎么来算出来，只有能够统计就行

[hdjliyisss]

[Quote=引用 8 楼 dawugui 的回复:]
SQL code
create table chukuhzb (bianhao varchar(10),chukujine int,shuliang int,nianyue datetime)
insert into chukuhzb values('001', 200 ,100 ,'2011-09-09')
insert into chukuhzb values('001', 300 ,……
[/Quote]
试试！大神膜拜

[dawugui]

[Quote=引用 9 楼 hdjliyisss 的回复:]
引用 1 楼 fredrickhu 的回复:
没看出来是怎么算出来的 225 和 150

不需用知道怎么来算出来，只有能够统计就行
[/Quote]难道我上面回复的不行?和他废话?

[hdjliyisss]

[Quote=引用 11 楼 dawugui 的回复:]
引用 9 楼 hdjliyisss 的回复:
引用 1 楼 fredrickhu 的回复:
没看出来是怎么算出来的 225 和 150

不需用知道怎么来算出来，只有能够统计就行
难道我上面回复的不行?和他废话?
[/Quote]
正在使用！O(∩_∩)O~

[hdjliyisss]

[Quote=引用 11 楼 dawugui 的回复:]
引用 9 楼 hdjliyisss 的回复:
引用 1 楼 fredrickhu 的回复:
没看出来是怎么算出来的 225 和 150

不需用知道怎么来算出来，只有能够统计就行
难道我上面回复的不行?和他废话?
[/Quote]

可以使用，谢谢了！！收分

[--小F--]

没看出来是怎么算出来的 225 和 150

[-晴天]

select a.bianhao,a.kucunjine,a.kucunshuliang,b.rukujine),b.rukushuliang,c.chukujine,c.chukushuliang
from(
select bianhao,sum(kucunjine)kucunjine,sum(kucunshuliang)kucunshuliang from 库存表 group by bianhao
)a left join (
select bianhao,sum(rukujine)rukujine,sum(rukushuliang)rukushuliang from 入库表 group by bianhao
)b on a.bianhao=b.bianhao left join (
select bianhao,sum(chukujine)chukujine,sum(chukushuliang)chukushuliang from 出库表 group by bianhao
)c on a.bianhao=c.bianhao

[dawugui]

       t1.chukujine,
       t1.shuliang,
       t2.kucunjine,
       t2.kucunshuliang,
       t3.chukujine,
       t3.chukushuliang
from
(select t.* from chukuhzb t where nianyue = (select max(nianyue) from chukuhzb where bianhao = t.bianhao)) t1
full join 
(select t.* from kucunhzb t where nianyue = (select max(nianyue) from kucunhzb where bianhao = t.bianhao)) on t1.bianhao = t2.bianhao
full join
(select t.* from rukuhzb t where nianyue = (select max(nianyue) from rukuhzb where bianhao = t.bianhao)) t3 on isnull(t1.bianhao,t2.bianhao) = t3.bianhao

[dawugui]

select isnull(isnull(t1.bianhao,t2.bianhao),t3.bianhao) bianhao,
       t1.chukujine,
       t1.shuliang,
       t2.kucunjine,
       t2.kucunshuliang,
       t3.chukujine,
       t3.chukushuliang
from
(select t.* from chukuhzb t where nianyue = (select max(nianyue) from chukuhzb where bianhao = t.bianhao)) t1
full join 
(select t.* from kucunhzb t where nianyue = (select max(nianyue) from kucunhzb where bianhao = t.bianhao)) on t1.bianhao = t2.bianhao
full join
(select t.* from rukuhzb t where nianyue = (select max(nianyue) from rukuhzb where bianhao = t.bianhao)) t3 on isnull(t1.bianhao,t2.bianhao) = t3.bianhao

[-晴天]

汇总统计不应该与出入库结帐混在一起,如果有出入库操作,那应该立即反映到库存表中(可以通过触发器或存储过程进行处理).如果要求对数据进行统计,那用楼上的统计语句.

[dawugui]

select isnull(isnull(t1.bianhao,t2.bianhao),t3.bianhao) bianhao,
       t2.kucunjine,
       t2.kucunshuliang,
       t3.rukujine,
       t3.rukushuliang,
       t1.chukujine,
       t1.shuliang
from
(select t.* from chukuhzb t where nianyue = (select max(nianyue) from chukuhzb where bianhao = t.bianhao)) t1
full join 
(select t.* from kucunhzb t where nianyue = (select max(nianyue) from kucunhzb where bianhao = t.bianhao)) t2 on t1.bianhao = t2.bianhao
full join
(select t.* from rukuhzb t where nianyue = (select max(nianyue) from rukuhzb where bianhao = t.bianhao)) t3 on isnull(t1.bianhao,t2.bianhao) = t3.bianhao
order by t1.bianhao

/*
bianhao    kucunjine   kucunshuliang rukujine    rukushuliang chukujine   shuliang    
---------- ----------- ------------- ----------- ------------ ----------- ----------- 
001        300         400           32          32           300         200
002        321         434           232         323          234         27

（所影响的行数为 2 行）
*/

[dawugui]

create table  chukuhzb (bianhao varchar(10),chukujine int,shuliang int,nianyue datetime)
insert into chukuhzb values('001', 200 ,100 ,'2011-09-09')
insert into chukuhzb values('001', 300 ,200 ,'2011-09-10')
insert into chukuhzb values('002', 204 ,23  ,'2011-09-10')
insert into chukuhzb values('002', 234 ,27  ,'2011-09-11')
insert into chukuhzb values('001', 225 ,150 ,'2011-09-28')
create table kucunhzb(bianhao varchar(10),kucunjine int,kucunshuliang int,nianyue datetime)
insert into kucunhzb values('001', 400 ,300 ,'2011-09-09')
insert into kucunhzb values('001', 300 ,400 ,'2011-09-10')
insert into kucunhzb values('002', 323 ,321 ,'2011-09-10')
insert into kucunhzb values('002', 321 ,434 ,'2011-09-11')
create table rukuhzb(bianhao varchar(10),rukujine int,rukushuliang int,nianyue datetime)
insert into rukuhzb values('001', 21  ,32  ,'2011-09-08')
insert into rukuhzb values('001', 32  ,32  ,'2011-09-10')
insert into rukuhzb values('002', 204 ,32  ,'2011-09-10')  --这里日期相同了,更改为2011-09-10
insert into rukuhzb values('002', 232 ,323 ,'2011-09-11')
go

select isnull(isnull(t1.bianhao,t2.bianhao),t3.bianhao) bianhao,
       t2.kucunjine,
       t2.kucunshuliang,
       t3.rukujine,
       t3.rukushuliang,
       t1.chukujine,
       t1.shuliang
from
(select t.* from chukuhzb t where nianyue = (select max(nianyue) from chukuhzb where bianhao = t.bianhao)) t1
full join 
(select t.* from kucunhzb t where nianyue = (select max(nianyue) from kucunhzb where bianhao = t.bianhao)) t2 on t1.bianhao = t2.bianhao
full join
(select t.* from rukuhzb t where nianyue = (select max(nianyue) from rukuhzb where bianhao = t.bianhao)) t3 on isnull(t1.bianhao,t2.bianhao) = t3.bianhao
order by t1.bianhao

/*
bianhao    kucunjine   kucunshuliang rukujine    rukushuliang chukujine   shuliang    
---------- ----------- ------------- ----------- ------------ ----------- ----------- 
001        300         400           32          32           225         150
002        321         434           232         323          234         27

（所影响的行数为 2 行）
*/

drop table chukuhzb,kucunhzb,rukuhzb

[dawugui]

create table  chukuhzb (bianhao varchar(10),chukujine int,shuliang int,nianyue datetime)
insert into chukuhzb values('001', 200 ,100 ,'2011-09-09')
insert into chukuhzb values('001', 300 ,200 ,'2011-09-10')
insert into chukuhzb values('002', 204 ,23  ,'2011-09-10')
insert into chukuhzb values('002', 234 ,27  ,'2011-09-11')
insert into chukuhzb values('001', 225 ,150 ,'2011-09-28')
create table kucunhzb(bianhao varchar(10),kucunjine int,kucunshuliang int,nianyue datetime)
insert into kucunhzb values('001', 400 ,300 ,'2011-09-09')
insert into kucunhzb values('001', 300 ,400 ,'2011-09-10')
insert into kucunhzb values('002', 323 ,321 ,'2011-09-10')
insert into kucunhzb values('002', 321 ,434 ,'2011-09-11')
create table rukuhzb(bianhao varchar(10),rukujine int,rukushuliang int,nianyue datetime)
insert into rukuhzb values('001', 21  ,32  ,'2011-09-08')
insert into rukuhzb values('001', 32  ,32  ,'2011-09-10')
insert into rukuhzb values('002', 204 ,32  ,'2011-09-10')  --这里日期相同了,更改为2011-09-10
insert into rukuhzb values('002', 232 ,323 ,'2011-09-11')
go

--如果你不能确保三表bianhao一一对应，建议使用full join
select isnull(isnull(t1.bianhao,t2.bianhao),t3.bianhao) bianhao,
       t2.kucunjine,
       t2.kucunshuliang,
       t3.rukujine,
       t3.rukushuliang,
       t1.chukujine,
       t1.shuliang
from
(select t.* from chukuhzb t where nianyue = (select max(nianyue) from chukuhzb where bianhao = t.bianhao)) t1
full join 
(select t.* from kucunhzb t where nianyue = (select max(nianyue) from kucunhzb where bianhao = t.bianhao)) t2 on t1.bianhao = t2.bianhao
full join
(select t.* from rukuhzb t where nianyue = (select max(nianyue) from rukuhzb where bianhao = t.bianhao)) t3 on isnull(t1.bianhao,t2.bianhao) = t3.bianhao
order by t1.bianhao
/*
bianhao    kucunjine   kucunshuliang rukujine    rukushuliang chukujine   shuliang    
---------- ----------- ------------- ----------- ------------ ----------- ----------- 
001        300         400           32          32           225         150
002        321         434           232         323          234         27

（所影响的行数为 2 行）
*/


--如果你能确保三表bianhao一一对应
--1
select t1.bianhao,
       t1.kucunjine,
       t1.kucunshuliang,
       t2.rukujine,
       t2.rukushuliang,
       t3.chukujine,
       t3.shuliang
from kucunhzb t1 , rukuhzb t2 , chukuhzb t3
where t1.bianhao = t2.bianhao and t1.bianhao = t3.bianhao
and t1.nianyue = (select max(nianyue) from kucunhzb where bianhao = t1.bianhao)
and t2.nianyue = (select max(nianyue) from rukuhzb where bianhao = t2.bianhao)
and t3.nianyue = (select max(nianyue) from chukuhzb where bianhao = t3.bianhao)
/*
bianhao    kucunjine   kucunshuliang rukujine    rukushuliang chukujine   shuliang    
---------- ----------- ------------- ----------- ------------ ----------- ----------- 
001        300         400           32          32           225         150
002        321         434           232         323          234         27

（所影响的行数为 2 行）
*/

--2
select t1.bianhao,
       t1.kucunjine,
       t1.kucunshuliang,
       t2.rukujine,
       t2.rukushuliang,
       t3.chukujine,
       t3.shuliang
from kucunhzb t1 , rukuhzb t2 , chukuhzb t3
where t1.bianhao = t2.bianhao and t1.bianhao = t3.bianhao
and not exists(select 1 from kucunhzb where bianhao = t1.bianhao and nianyue > t1.nianyue)
and not exists(select 1 from rukuhzb where bianhao = t2.bianhao and nianyue > t2.nianyue)
and not exists(select 1 from chukuhzb where bianhao = t3.bianhao and nianyue > t3.nianyue)
/*
bianhao    kucunjine   kucunshuliang rukujine    rukushuliang chukujine   shuliang    
---------- ----------- ------------- ----------- ------------ ----------- ----------- 
001        300         400           32          32           225         150
002        321         434           232         323          234         27

（所影响的行数为 2 行）
*/


drop table chukuhzb,kucunhzb,rukuhzb