订单表 tb1
id 价位 数量 金额
1 100.00 1 100.00
2 100.00 2 200.00
3 100.00 3 300.00
4 80.00 2 160.00
5 80.00 4 320.00
价格表 tb2
id 价位
1 100.00
2 80.00
行转列后统计结果
价位 数量 金额
100.00 6 600.00
80.00 6 480.00
仅剩80分了，分数不多，望各位大神出手帮助。
------解决方案--------------------
----------------------------------------------------------------
-- 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)
*/
------解决方案--------------------
不需要行转列，只需要group by求和就行：
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
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