一个求和的sql语句–sum
表描述：
表名：Sum_Test
Id Name Count Type
1 A 12 1
2 B 12 1
3 C 12 1
4 D 12 1
5 B 12 2
6 B 12 1
7 A 12 2
8 A 12 1
9 C 12 1
10 B 12 1
结果： Type 1 ： 84
Type 2 ： 36
求type 为1和2的Count的总和，如果Name即有Type为1也有Type为2的，则该Count算到Type为2的里面，如： A
Type即有1也有2则A的求和时将A的Count值全部算作为Type=2里面
方法一：
现将Type为2的Name的Type值都改为2
UPDATE Sum_Test SET Type=2 WHERE Name IN (SELECT Name FROM Sum_Test SHERE Type=2)
然后再求和
SELECT SUM(Count) FROM Sum_Test GROUP BY Type
方法二：
select sum([count]) from
(
select [count],[Type]=’2′ from sum_test where [name] in (select [name] from sum_test where type=2)
UNION ALL
select [count],[Type]=’1′ from sum_test where [name] not in (select [name] from sum_test where type=2)
) t
group by [type]
这两种方法虽然可以得到想要的结果，但是有很大的弊端
方法三：
select (select sum([count]) from sum_test) – (select sum(t.counts) from
(select []name, sum([count]) counts from sum_test
where [name] in(select [name] from sum_test where [type] = 2)
group by [name]) t) type1, (select sum(t.counts) from
(select [name], sum([count]) counts from sum_test
where [name] in(select [name] from sum_test where [type] = 2)
group by [name]) t) type2
写成一个过程的话我想执行效率会更好!
代码:
declare @SubTotal int,
@Total int
set @SubTotal = (select sum(t.counts) from
(select [name], sum([count]) counts from sum_test
where [name] in(select [name] from sum_test where [type] = 2)
group by [name]) t)
set @Total = (select sum([count]) from sum_test)
select @Total – @SubTotal type1, @SubTotal type2
绿色通道： 好文要顶 关注我 收藏该文与我联系
心翼
关注 – 1
粉丝 – 14
+加关注
0
0
(请您对文章做出评价)
» 下一篇：_cdecl 和_stdcall
posted on 2010-09-02 16:34 心翼 阅读(6588) 评论(2) 编辑 收藏
评论
#1楼2010-09-02 17:32killkill
貌似你的描述错了，Type 1 和 Type 2 的和算反了。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
with Sum_Test(id,name,[count],type) as (
select 1,’A',12,1 union all
select 2,’B',12,1 union all
select 3,’C',12,1 union all
select 4,’D',12,1 union all
select 5,’B',12,2 union all
select 6,’B',12,1 union all
select 7,’A',12,2 union all
select 8,’A',12,1 union all
select 9,’C',12,1 union all
select 10,’B',12,1
)
select — ‘Type’+cast(new_type as varchar)+’: ‘+ cast(sum(sum_count) as varchar)
new_type,sum(sum_count)
from
(
select
name,type,sum([count]) sum_count ,count(*) over(partition by name) type_count,
case when count(*) over(partition by name)=2 then 2 else type end new_type
from Sum_Test
group by name,type
) a
group by new_type
>> RESULT:
new_type sum
———– ———–
1 36
2 84
http://www.cnblogs.com/52yixin/archive/2010/09/02/1816179.html