给个思路SELECT 商品ID,SUM(数量) AS 数量 FROM
(
SELECT 商品ID,SUM(入库数量) AS 数量
FROM 商品入库 GROUP BY 商品ID WHERE 入库日期..... UNION ALL SELECT 商品ID,SUM( - 出库数量) AS 数量
FROM 商品出库 GROUP BY 商品ID WHERE 出库日期.....
)
(
SELECT 商品ID,SUM(入库数量) AS 数量
FROM 商品入库 GROUP BY 商品ID WHERE 入库日期..... UNION ALL SELECT 商品ID,SUM( - 出库数量) AS 数量
FROM 商品出库 GROUP BY 商品ID WHERE 出库日期.....
)
QQ33524826
(
SELECT 商品ID,SUM(入库数量) AS 数量
FROM 商品入库 WHERE datediff(month,入库日期,getdate())=0
UNION ALL
SELECT 商品ID,-SUM(出库数量) AS 数量
FROM 商品出库 WHERE datediff(month,入库日期,getdate())=0
) tem GROUP BY 商品ID----------------------
group by 在最后面。
(
SELECT 商品ID,SUM(入库数量) AS 数量
FROM 商品入库
UNION ALL
SELECT 商品ID,-SUM(出库数量) AS 数量
FROM 商品出库
) tem GROUP BY 商品ID
Select sum(a.数量-b.数量) as 数量
From 商品入库 a left join 商品出库 b
on a.商品ID=b.商品ID
where 时间条件
我认为你最好在 商品出库 表上建一个触发器
当插入,修改,删除 记录后就在 商品入库中减掉,增加 相应的数量
Create Trigger Trg1 on 商品出库 for delete,insert,update
as
begin
declare @Count int,@ID int --总数量,商品ID
declare Cur1 Cursor for (select sum(数量),商品ID as 数量 from deleted group by 商品ID)
Open Cur1
Fetch next from Cur1 into @count,@ID
while @@Fetch_Status=0
begin
update 商品入库 set 数量=数量+@Count
where 商品ID=@ID
Fetch next from Cur1 into @count,@ID
end
close Cur1
deallocate Cur1
declare Cur2 Cursor for (select sum(数量),商品ID as 数量 from Inserted group by 商品ID)
Open Cur2
Fetch next from Cur2 into @count,@ID
while @@Fetch_Status=0
begin
update 商品入库 set 数量=数量-@Count
where 商品ID=@ID
Fetch next from Cur2 into @count,@ID
end
close Cur2
deallocate Cur2
end 利润就很好做了
select c.商品名称,sum(a.数量)*(b.单价-a.价格) as 利润
from 商品出库 a,商品入库 b,商品资料 c
where a.商品ID=b.商品ID and a.出货日期 between 日期1 and 日期2
and b.商品ID=c.商品ID
group by c.商品名称