table a item 1 itme2 item3
1 30 2012-02
2 50 2012-02
3 20 2012-04现在导出item1 item2 item3 合计
1 30 2012-02 80
2 50 2012-02 80
3 20 2012-04 20
1 30 2012-02
2 50 2012-02
3 20 2012-04现在导出item1 item2 item3 合计
1 30 2012-02 80
2 50 2012-02 80
3 20 2012-04 20
with a(item1,item2,item3)as(
select 1,30,'2012-02' union all
select 2,50,'2012-02' union all
select 3,20,'2012-04'
)
select *,合计=SUM(item2) over(partition by item3 ) from asqlserver2008下可以用
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-05-13 15:01:04
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([item1] int,[itme2] int,[item3] varchar(7))
insert [a]
select 1,30,'2012-02' union all
select 2,50,'2012-02' union all
select 3,20,'2012-04'
--------------开始查询--------------------------select item1, A.[itme2] , A.item3,B.itme2 AS [合计]
from [a] A INNER JOIN (SELECT SUM([itme2])[itme2],item3 FROM [A] GROUP BY item3 ) B
ON A.item3=B.item3
----------------结果----------------------------
/*
item1 itme2 item3 合计
----------- ----------- ------- -----------
1 30 2012-02 80
2 50 2012-02 80
3 20 2012-04 20
*/
select a.item1,a.itme2,a.item3,b.合计 from AA a join (select sum(itme2) as 合计,item3 from AA group by item3) b
on a.item3=b.item3