--进货表
declare @a table(进货单 int ,供应商_id varchar(2),商品_id varchar(1),定价 decimal(9,3),进货数 int,
折扣 decimal(5,3),jh_time datetime)
insert into @a
select '1','A1','B',10,100,0.6,'2006-01-01'union all
select '2','A2','B',10,50,0.7,'2006-02-01'union all
select '3','A1','B',10,100,0.6,'2006-03-01'
--销售表
declare @b table (销售单_id varchar(2),商品_id varchar(1),定价 decimal(9,3),销售数量 int,
折扣 decimal(5,3),jh_time datetime)
insert into @b
select '11','B',10,80,0.9,'2006-01-02'union all
select '22','B',10,60,0.8,'2006-02-02'union all
select '33','B',10,40,1,'2006-03-02'--统计结果供应商 销量 实洋
A1 130 1180.00
A2 50 420.00
declare @a table(进货单 int ,供应商_id varchar(2),商品_id varchar(1),定价 decimal(9,3),进货数 int,
折扣 decimal(5,3),jh_time datetime)
insert into @a
select '1','A1','B',10,100,0.6,'2006-01-01'union all
select '2','A2','B',10,50,0.7,'2006-02-01'union all
select '3','A1','B',10,100,0.6,'2006-03-01'
--销售表
declare @b table (销售单_id varchar(2),商品_id varchar(1),定价 decimal(9,3),销售数量 int,
折扣 decimal(5,3),jh_time datetime)
insert into @b
select '11','B',10,80,0.9,'2006-01-02'union all
select '22','B',10,60,0.8,'2006-02-02'union all
select '33','B',10,40,1,'2006-03-02'--统计结果供应商 销量 实洋
A1 130 1180.00
A2 50 420.00
进货单号,供应商_id,商品_id,定价,进货数,
折扣,jh_time (进货时间)
1 A1 B 10.000 100 .600 2006-01-01 00:00:00.000
2 A2 B 10.000 50 .700 2006-02-01 00:00:00.000
3 A1 B 10.000 100 .600 2006-03-01 00:00:00.000
销售表
销售单_id 商品_id 定价 ,销售数量 ,折扣 jh_time (销售时间))
11 B 10.000 80 .900 2006-01-02 00:00:00.000
22 B 10.000 60 .800 2006-02-02 00:00:00.000
33 B 10.000 40 1.000 2006-03-02 00:00:00.000
A1 130 1180.00
A2 50 420.00
http://topic.csdn.net/t/20050530/10/4045247.html
参考, 顶不住了 :)
create table 进货表
(进货单 int ,供应商_id varchar(2),商品_id varchar(1),定价 decimal(9,3),进货数 int,
折扣 decimal(5,3),jh_time datetime)
insert into 进货表
select '1','A1','B',10,100,0.6,'2006-01-01'union all
select '2','A2','B',10,50,0.7,'2006-02-01'union all
select '3','A1','B',10,100,0.6,'2006-03-01'
--销售表
create table 销售表(销售单_id varchar(2),商品_id varchar(1),定价 decimal(9,3),销售数量 int,
折扣 decimal(5,3),jh_time datetime)
insert into 销售表
select '11','B',10,80,0.9,'2006-01-02'union all
select '22','B',10,60,0.8,'2006-02-02'union all
select '33','B',10,40,1,'2006-03-02'select * from 进货表
SELECT * FROM 销售表select 供应商_id,sales=(case when s销售数量>s进货数 then s进货数 else s销售数量 end)-(case when s销售数量-销售数量<s进货数-进货数 then s进货数-进货数 else s销售数量-销售数量 end)
into #T from ( select *,s销售数量=( select sum(销售数量)
from 销售表
where 销售单_id <=a.销售单_id and 商品_id=a.商品_id
)
from 销售表 a
) b
join
( select *,s进货数=( select sum(进货数)
from 进货表
where 商品_id=a.商品_id and 进货单<=a.进货单
)
from 进货表 a
) c
on b.商品_id=c.商品_id and s销售数量-销售数量<s进货数 and s销售数量>s进货数-进货数
order by 销售单_id,b.商品_id,供应商_idgoselect 供应商_id,sum(sales) as sales from #T group by 供应商_id--结果
供应商_id sales
A1 130
A2 50那个什么实洋不知道怎么计算~ 根据折扣来算么,怎么都算不对~