select aa.goods_id,
aa.sum(number) as [库存],
(select count(*)
from sale
where datediff(day,sale_time,getdate())=1 and goods_id=aa.goods_id)
as [前一天销售数量]
from (select goods_id,mumber from stock union all
select goods_id,-(count(*)) as number
from sale
group by good_id ) aa
group by aa.goods_id
aa.sum(number) as [库存],
(select count(*)
from sale
where datediff(day,sale_time,getdate())=1 and goods_id=aa.goods_id)
as [前一天销售数量]
from (select goods_id,mumber from stock union all
select goods_id,-(count(*)) as number
from sale
group by good_id ) aa
group by aa.goods_id
,昨天销售量=b.数量,昨天销售额=b.销售额
from stock a left join (
select goods_id,数量=count(*),销售额=sum(price) from sale a
group by goods_id)b on a.goods_id=b.goods_id
--测试:
create table stock(goods_id int,number int)
insert into stock
select 1,5 union all
select 2,15 union all
select 3,5 union all
select 4,8 union all
select 5,10 union all
select 6,121
create table sale(goods_id int,price int,sale_time datetime)
insert into sale
select 1,10,'2004-11-03' union all
select 2,15,'2004-11-03' union all
select 4,20,'2004-11-03' union all
select 5,12,'2004-11-03' union all
select 6,18,'2004-11-03' union all
select 2,15,'2004-11-03' union all
select 1,10,'2004-11-03' union all
select 1,23,'2004-11-5'
--查询:
select a.goods_id,原数量=a.number,现有数量=a.number-b.数量
,昨天销售量=b.数量,昨天销售额=b.销售额
from stock a left join (
select goods_id,数量=count(*),销售额=sum(price) from sale a
where dateadd(day,1,sale_time)=convert(varchar(10),getdate(),120)
group by goods_id )b on a.goods_id=b.goods_id drop table stock,sale
--结果:
goods_id 原数量 现有数量 昨天销售量 昨天销售额
----------- ----------- ----------- ----------- -----------
1 5 3 2 20
2 15 13 2 30
3 5 NULL NULL NULL
4 8 7 1 20
5 10 9 1 12
6 121 120 1 18--注意,因为现在是2004-11-9,按上面的数据,你是查不出结果的。
调一下机器系统时间为 2004-11-04试下,结果如上。不需要的列可以去掉。