日期:2014-05-17 浏览次数:20629 次
----------------------------
-- Author :daxionglee(物是人非事事休,欲语泪先流!)
-- Date :2012-12-13 13:23:08
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据:[商品表]
if object_id('[商品表]') is not null
drop table [商品表]
go
create table [商品表]
(
[ID] int,
[商品名称] varchar(5),
[售价] int,
[会员价] int
)
insert [商品表]
select 1,'商品1',20,15 union all
select 2,'商品2',30,25
--> 测试数据:[库存表]
if object_id('[库存表]') is not null
drop table [库存表]
go
create table [库存表]
(
[ID] int,
[进货单号] varchar(4),
[进货数量] int,
[进货价格] int,
[AID] int
)
insert [库存表]
select 1,'j001',10,10,1 union all
select 2,'j002',15,11,1 union all
select 3,'j003',15,11,2
go
select
a.*,
b.总进货价格,
b.总进货数量
from
[商品表] as a
inner join
(
select
[AID],
sum([进货数量]*[进货价格]) as [总进货价格],
SUM([进货数量]) as [总进货数量]
from
[库存表]
group by
[AID]
) as b
on
a.ID=b.AID
/*
ID 商品名称 售价 会员价 总进货价格 总进货数量
----------- ----- ----------- ----------- ----------- -----------
1 商品1 20 15 265 25
2 商品2 30 25 165 15
(2 行受影响)
*/
库存什么的 怎么算 表给全