----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-22 17:14:37
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[ tb1]
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([id] int,[价位] numeric(5,2),[数量] int,[金额] numeric(5,2))
insert [tb1]
select 1,100.00,1,100.00 union all
select 2,100.00,2,200.00 union all
select 3,100.00,3,300.00 union all
select 4,80.00,2,160.00 union all
select 5,80.00,4,320.00
--> 测试数据:[tb2]
if object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2]([id] int,[价位] numeric(5,2))
insert [tb2]
select 1,100.00 union all
select 2,80.00
--------------开始查询--------------------------select [tb2].价位,[tb1].数量,金额
from [tb2] INNER JOIN (
select SUM([数量])[数量],SUM([金额])[金额],[价位] from [tb1] GROUP BY [价位]) [tb1] ON [tb2].[价位]=[tb1].[价位]
----------------结果----------------------------
/*
价位 数量 金额
--------------------------------------- ----------- ---------------------------------------
80.00 6 480.00
100.00 6 600.00
*/
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-22 17:14:37
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[ tb1]
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([id] int,[价位] numeric(5,2),[数量] int,[金额] numeric(5,2))
insert [tb1]
select 1,100.00,1,100.00 union all
select 2,100.00,2,200.00 union all
select 3,100.00,3,300.00 union all
select 4,80.00,2,160.00 union all
select 5,80.00,4,320.00
--> 测试数据:[tb2]
if object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2]([id] int,[价位] numeric(5,2))
insert [tb2]
select 1,100.00 union all
select 2,80.00
--------------开始查询--------------------------select [tb2].价位,[tb1].数量,金额
from [tb2] INNER JOIN (
select SUM([数量])[数量],SUM([金额])[金额],[价位] from [tb1] GROUP BY [价位]) [tb1] ON [tb2].[价位]=[tb1].[价位]
----------------结果----------------------------
/*
价位 数量 金额
--------------------------------------- ----------- ---------------------------------------
80.00 6 480.00
100.00 6 600.00
*/
select 价位,
sum(数量) '数量',
sum(金额) '金额'
from tb1
group by 价位/*
价位 数量 金额
--------------------------------------- ----------- ---------------------------------------
80.00 6 480.00
100.00 6 600.00(2 row(s) affected)
*/
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([id] int,[价位] numeric(5,2),[数量] int,[金额] numeric(5,2))
insert [tb1]
select 1,100.00,1,100.00 union all
select 2,100.00,2,200.00 union all
select 3,100.00,3,300.00 union all
select 4,80.00,2,160.00 union all
select 5,80.00,4,320.00if object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2]([id] int,[价位] numeric(5,2))
insert [tb2]
select 1,100.00 union all
select 2,80.00
go
select [价位],
sum([数量]) as 数量,
sum([金额]) as 金额
from tb1
group by [价位]
/*
价位 数量 金额
80.00 6 480.00
100.00 6 600.00
*/
sum(数量) '数量',
sum(金额) '金额'
from tb1
group by 价位
b.价位,
数量 = SUM(a.数量),
金额 = SUM(a.金额)
FROM tb2 a
INNER JOIN tb1 b
ON a.价位 = b.价位
GROUP BY b.价位
ORDER BY MIN(b.id)