sql统计汇总问题
Table1 /* 进货表*/
Name Type Unit Number Price TotalPrice
联想 台式机 台 10 3000 30000
华硕 台式机 台 5 3000 15000
Table2 /*销售表*/
Name Type Unit Number Price TotalPrice
联想 台式机 台 1 3500 3500
联想 台式机 台 2 4000 8000
华硕 台式机 台 2 2500 5000
如何计算出 中间的利润值显示在一张表上?
请各位帮忙。
------解决方案------------------------------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-03-20 10:57:26
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[Table1]
if object_id('[Table1]') is not null drop table [Table1]
go
create table [Table1]([Name] varchar(4),[Type] varchar(6),[Unit] varchar(2),[Number] int,[Price] int,[TotalPrice] int)
insert [Table1]
select '联想','台式机','台',10,3000,30000 union all
select '华硕','台式机','台',5,3000,15000
--> 测试数据:[Table2]
if object_id('[Table2]') is not null drop table [Table2]
go
create table [Table2]([Name] varchar(4),[Type] varchar(6),[Unit] varchar(2),[Number] int,[Price] int,[TotalPrice] int)
insert [Table2]
select '联想','台式机','台',1,3500,3500 union all
select '联想','台式机','台',2,4000,8000 union all
select '华硕','台式机','台',2,2500,5000
--------------开始查询--------------------------
SELECT
a.name,a.Type,a.Unit,b.Number,a.Price AS 原价,b.price AS 现价,(ISNULL(b.Price,0)-ISNULL(a.Price,0))*b.Number AS 利润,b.TotalPrice
FROM
table1 AS a
INNER JOIN
table2 AS b
ON
a.Name=b.NAME AND a.type=b.TYPE AND a.Unit=b.unit
----------------结果----------------------------
/* name Type Unit Number 原价