我有 buy 表 (进货表)
id number total(金额) date
1 3 30 2006-03-01 11:23:52 1 4 40 2006-03-01 16:22:01
2
2 。
有个 sale(出货表)
id number stotal(金额) date
1 2 30 2006-03-01 20:00:00 1 2 50 2006-03-01 21:00:00
有keep表(库存表)
id number total(金额) date
1 1 10 2006-03-01 11:23:52 1 2 20 2006-03-01 16:22:01
。 我现在想 ,按日子把详细情况输出来
像这样
date, id ,进货总数,进货金额,出货总数量,出货金额,库存总数量,库存金额
2006-03-01 1 7 70 4 80 3 30
按日子进货的一直下去
在等 ,挺急的,先谢谢了。
id number total(金额) date
1 3 30 2006-03-01 11:23:52 1 4 40 2006-03-01 16:22:01
2
2 。
有个 sale(出货表)
id number stotal(金额) date
1 2 30 2006-03-01 20:00:00 1 2 50 2006-03-01 21:00:00
有keep表(库存表)
id number total(金额) date
1 1 10 2006-03-01 11:23:52 1 2 20 2006-03-01 16:22:01
。 我现在想 ,按日子把详细情况输出来
像这样
date, id ,进货总数,进货金额,出货总数量,出货金额,库存总数量,库存金额
2006-03-01 1 7 70 4 80 3 30
按日子进货的一直下去
在等 ,挺急的,先谢谢了。
id int,
number int,
total int,
[date] datetime
)
create table sale(
id int,
number int,
stotal int,
date datetime)
create table [keep](
id int,
number int,
total int,
date datetime
)
insert buy select 1,3,30,'2006-03-01 11:23:52'
union all select 1,4 , 40,'2006-03-01 16:22:01'
insert sale select 1 , 2 , 30 , '2006-03-01 20:00:00'
union all select 1 , 2 , 50 , '2006-03-01 21:00:00'insert [keep] select 1,1,10,'2006-03-01 11:23:52'
union all select 1 , 2 , 20 , '2006-03-01 16:22:01'
select B.id,B.Bdate,B.进货金额,B.进货总数,S.出货总数量,S.出货总金额,K.库存总数量,K.库存金额
from
(select id,Bdate=Year(date)+'-'+Month(Date)+'-'+Day(Date),进货金额=sum(total),进货总数=sum(number) from buy group by id,Year(date)+'-'+Month(Date)+'-'+Day(Date))B,
(select id,Sdate=Year(date)+'-'+Month(Date)+'-'+Day(Date),出货总数量=sum(number),出货总金额=sum(stotal) from sale group by id,Year(date)+'-'+Month(Date)+'-'+Day(Date))S,
(select id,Kdate=Year(date)+'-'+Month(Date)+'-'+Day(Date),库存总数量=sum(number),库存金额=sum(total) from keep group by id,Year(date)+'-'+Month(Date)+'-'+Day(Date))K
Where B.id = S.id And S.id=K.id and B.id=K.id
drop table buy,sale,[keep]
from
(select id,Bdate=Year(date)+'-'+Month(Date)+'-'+Day(Date),进货金额=sum(total),进货总数=sum(number) from buy group by id,Year(date)+'-'+Month(Date)+'-'+Day(Date))B,
(select id,出货总数量=sum(number),出货总金额=sum(stotal) from sale group by id)S,
(select id,库存总数量=sum(number),库存金额=sum(total) from keep group by id)K
Where B.id = S.id And S.id=K.id and B.id=K.id
drop table buy,sale,[keep]
/*
id Bdate 进货金额 进货总数 出货总数量 出货总金额 库存总数量 库存金额
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 2010 70 7 4 80 3 30(所影响的行数为 1 行)
*/
日期好像出了问题