name value1 value2 value3
a 1 2 3
b 2 2 3
c 1 2 3
d 1 2 1
e 1 2 1
f 1 2 2想得到结果是:
name value1 value2 value3
a 1 2 3
b 2 2 3
除a,b外 4 8 7XX各位
a 1 2 3
b 2 2 3
c 1 2 3
d 1 2 1
e 1 2 1
f 1 2 2想得到结果是:
name value1 value2 value3
a 1 2 3
b 2 2 3
除a,b外 4 8 7XX各位
union all
select name = '除a,b外',value1=sum(value1),value2=sum(value2),value3=sum(value3)
from tab
where name not in ('a','b')
--> Author : js_szy
--> Target : 各位大大,小卒就是想要一朵花
--> Date : 2009-12-02 14:15:06
--> Version: SQL Server 2005
---------------------------------------------
--> 测试数据: @tb
declare @tb table (name varchar(1),value1 int,value2 int,value3 int)
insert into @tb
select 'a',1,2,3 union all
select 'b',2,2,3 union all
select 'c',1,2,3 union all
select 'd',1,2,1 union all
select 'e',1,2,1 union all
select 'f',1,2,2select
name=case when name!='a' and name!='b' then '除a,b外' else name end,
value1=sum(value1),
value2=sum(value2),
value3=sum(value3)
from @tb
group by case when name!='a' and name!='b' then '除a,b外' else name endname value1 value2 value3
------- ----------- ----------- -----------
a 1 2 3
b 2 2 3
除a,b外 4 8 7(3 行受影响)
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-02 14:15:03
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([name] varchar(1),[value1] int,[value2] int,[value3] int)
insert [tb]
select 'a',1,2,3 union all
select 'b',2,2,3 union all
select 'c',1,2,3 union all
select 'd',1,2,1 union all
select 'e',1,2,1 union all
select 'f',1,2,2
--------------开始查询--------------------------
select * from tb where name in ('a','b')
union all
select '除a,b外' as [name],sum(value1) as value1,sum(value2) as value2,sum(value3) as value3
from tb
where name not in ('a','b')
----------------结果----------------------------
/* name value1 value2 value3
------- ----------- ----------- -----------
a 1 2 3
b 2 2 3
除a,b外 4 8 7(3 行受影响)
*/
from ( select case when name in ('a','b') then name else '除a,b外' end ,value1,value2,value3 from tab) a
group by name这样也可以
insert into tb
select 'a',1,2,3 union all
select 'b',2,2,3 union all
select 'c',1,2,3 union all
select 'd',1,2,1 union all
select 'e',1,2,1 union all
select 'f',1,2,2select * from tb where name in ('a' , 'b')
union all
select '除a,b外' name , value1 = sum(value1) , value2 = sum(value2) , value3 = sum(value3) from tb where name not in ('a' , 'b')drop table tb/*
name value1 value2 value3
------- ----------- ----------- -----------
a 1 2 3
b 2 2 3
除a,b外 4 8 7(所影响的行数为 3 行)
*/