表描述：
表名：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
