34,588
社区成员
发帖
与我相关
我的任务
分享
declare @sql nvarchar(4000)
select @sql='SELECT [类别名]=t2.TYPE,[进货额]=SUM(Buy),[销售额]=SUM(Sell),[毛利]=SUM(Sell)-SUM(Buy)
FROM @tab2 t2 LEFT JOIN @tab1 t1 ON t2.NAME = t1.NAME
GROUP BY t2.TYPE '
exec(@sql)
--> 测试数据:[销售表]
if object_id('[销售表]') is not null drop table [销售表]
go
create table [销售表]([Name] varchar(5),[ID] int,[Sell] int,[Type] varchar(2),[Date] datetime)
insert [销售表]
select '产品1',1,2,'书','2010-11-01' union all
select '产品2',2,2,'书','2010-11-07' union all
select '产品3',3,8,'笔','2010-11-18' union all
select '产品4',4,19,'花','2010-11-20'
--> 测试数据:[进货表]
if object_id('[进货表]') is not null drop table [进货表]
go
create table [进货表]([Name] varchar(5),[ID] int,[Buy] int,[Type] varchar(2),[Date] datetime)
insert [进货表]
select '产品1',1,10,'书','2010-11-1' union all
select '产品2',2,20,'书','2010-11-10' union all
select '产品3',3,30,'笔','2010-11-14'
--------------开始查询--------------------------
select a.[Type],sum([Buy])进货额, sum([Sell])销售额,sum([Sell])-sum([Buy]) 毛利
from [进货表] a, [销售表] b where a.[Name]=b.[Name]
and b.[Date] between '2010-11-1' and '2010-11-11'
group by a.[Type]
----------------结果----------------------------
/*
Type 进货额 销售额 毛利
---- ----------- ----------- -----------
书 30 4 -26
(1 行受影响)
*/
DECLARE @tab1 TABLE(NAME VARCHAR(10), ID INT, Sell INT, TYPE VARCHAR(5), Date VARCHAR(20))
DECLARE @tab2 TABLE(NAME VARCHAR(10), ID INT, Buy INT ,TYPE VARCHAR(5), Date VARCHAR(20))
INSERT INTO @tab1
SELECT '产品1', 1, 2, '书', '2010-11-01' UNION ALL
SELECT '产品2', 2, 2, '书', '2010-11-07' UNION ALL
SELECT '产品3', 3, 8, '笔', '2010-11-18' UNION ALL
SELECT '产品4', 4, 19, '花', '2010-11-20'
INSERT INTO @tab2
SELECT '产品1', 1, 10, '书', '2010-11-1' UNION ALL
SELECT '产品2', 2, 20, '书', '2010-11-10' UNION ALL
SELECT '产品3', 3, 30, '笔', '2010-11-14'
SELECT [类别名]=t2.TYPE,[进货额]=SUM(Buy),[销售额]=SUM(Sell),[毛利]=SUM(Sell)-SUM(Buy)
FROM @tab2 t2 LEFT JOIN @tab1 t1 ON t2.NAME = t1.NAME
GROUP BY t2.TYPE
/*
类别名 进货额 销售额 毛利
----- ----------- ----------- -----------
笔 30 8 -22
书 30 4 -26
(2 行受影响)
*/