select colem,sum(jiage),sum(shuliang) from tb group by colem
select colem,sum(jiage)jiage,sum(shuliang)shuliang from tb group by colem union all select N'集',sum(jiage),sum(shuliang) from tb
后面再加个 union all select '集',sum(jiage),sum(shuliang) from tb
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2011-03-17 16:48:48 -- Verstion: -- 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.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([colem] varchar(2),[jiage] int,[shuliang] int) insert [tb] select 'q',12,1 union all select 'a',10,2 union all select 'a',2,2 union all select 'e',3,6 --------------开始查询-------------------------- select isnull(colem,'集') as colem,sum([jiage]) as [jiage],sum([shuliang]) as [shuliang] from tb group by colem with rollup ----------------结果---------------------------- /* colem jiage shuliang ----- ----------- ----------- a 12 4 e 3 6 q 12 1 集 27 11(4 行受影响) */
insert into tb select * from ta
select colem,sum(jiage)jiage,sum(shuliang)shuliang from tb group by colem union all select N'集',sum(jiage),sum(shuliang) from tb
if object_id('tb') is not null drop table tb gocreate table tb ( colem varchar(20), jiage int, shuliang int ) GO insert into tb select 'q',12,1 union all select 'a',10,2 union all select 'a',2,2 union all select 'e',3,6 Go select colem,sum(jiage),sum(shuliang) from tb group by colem union all select CAST( count(*) as varchar(10)) as 名称,sum(jiage),sum(shuliang) from tb Go
use tempdb; /* create table test ( colem nvarchar(10) not null, jiage int not null, shuliang int not null ); insert into test(colem,jiage,shuliang) values ('q',12,1), ('a',10,2), ('a',2,2), ('e',3,6); */select t.colem,SUM(t.jiage) as jiage,SUM(t.shuliang) as shuliang from test as t group by t.colem union all select '集',SUM(jiage),SUM(shuliang) from test;
SELECT * FROM ( SELECT colem,SUM(jiage) AS jiage,SUM(shuliang) AS shuliang FROM ( SELECT 'q' AS colem,12 AS jiage,1 AS shuliang UNION ALL SELECT 'a' AS colem,10 AS jiage,2 AS shuliang UNION ALL SELECT 'a' AS colem,2 AS jiage,2 AS shuliang UNION ALL SELECT 'e' AS colem,3 AS jiage,6 AS shuliang ) AS T GROUP BY colem ) AS F COMPUTE SUM(jiage), SUM(shuliang)
/* SELECT * INTO ##tt FROM( SELECT 'q' AS colem,12 AS jiage,1 AS shuliang UNION ALL SELECT 'a' AS colem,10 AS jiage,2 AS shuliang UNION ALL SELECT 'a' AS colem,2 AS jiage,2 AS shuliang UNION ALL SELECT 'e' AS colem,3 AS jiage,6 AS shuliang ) AS a */ SELECT * FROM ( SELECT colem,SUM(jiage) AS jiage,SUM(shuliang) AS shuliang FROM ##tt GROUP BY colem ) AS F COMPUTE SUM(jiage), SUM(shuliang)--DROP TABLE ##tt
SELECT * FROM ( SELECT colem,SUM(jiage) AS jiage,SUM(shuliang) AS shuliang FROM ##tt GROUP BY colem ) AS F COMPUTE SUM(jiage), SUM(shuliang)
DECLARE @t TABLE(col CHAR(2),jiage INT, shuliang INT) INSERT @t ( col, jiage, shuliang ) VALUES ( 'q', -- col - char(2) 12, -- jiage - int 1 -- shuliang - int ) INSERT @t ( col, jiage, shuliang ) VALUES ( 'a', -- col - char(2) 10, -- jiage - int 2 -- shuliang - int ) INSERT @t ( col, jiage, shuliang ) VALUES ( 'a', -- col - char(2) 2, -- jiage - int 2 -- shuliang - int ) INSERT @t ( col, jiage, shuliang ) VALUES ( 'e', -- col - char(2) 3, -- jiage - int 6 -- shuliang - int )SELECT col,SUM(jiage),SUM(shuliang) FROM @t GROUP BY col WITH ROLLUP
select colem,sum(jiage),sum(shuliang) from tb group by colem
select colem,sum(jiage)jiage,sum(shuliang)shuliang
from tb
group by colem
union all
select N'集',sum(jiage),sum(shuliang)
from tb
union all select '集',sum(jiage),sum(shuliang) from tb
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-03-17 16:48:48
-- Verstion:
-- 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.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([colem] varchar(2),[jiage] int,[shuliang] int)
insert [tb]
select 'q',12,1 union all
select 'a',10,2 union all
select 'a',2,2 union all
select 'e',3,6
--------------开始查询--------------------------
select
isnull(colem,'集') as colem,sum([jiage]) as [jiage],sum([shuliang]) as [shuliang]
from
tb
group by
colem with rollup
----------------结果----------------------------
/* colem jiage shuliang
----- ----------- -----------
a 12 4
e 3 6
q 12 1
集 27 11(4 行受影响)
*/
from tb
group by colem
union all
select N'集',sum(jiage),sum(shuliang)
from tb
drop table tb
gocreate table tb
(
colem varchar(20),
jiage int,
shuliang int
)
GO
insert into tb
select 'q',12,1 union all
select 'a',10,2 union all
select 'a',2,2 union all
select 'e',3,6
Go
select colem,sum(jiage),sum(shuliang)
from tb group by colem
union all
select CAST( count(*) as varchar(10)) as 名称,sum(jiage),sum(shuliang) from tb
Go
use tempdb;
/*
create table test
(
colem nvarchar(10) not null,
jiage int not null,
shuliang int not null
);
insert into test(colem,jiage,shuliang)
values
('q',12,1),
('a',10,2),
('a',2,2),
('e',3,6);
*/select t.colem,SUM(t.jiage) as jiage,SUM(t.shuliang) as shuliang
from test as t
group by t.colem
union all
select '集',SUM(jiage),SUM(shuliang)
from test;
(
SELECT colem,SUM(jiage) AS jiage,SUM(shuliang) AS shuliang FROM
(
SELECT 'q' AS colem,12 AS jiage,1 AS shuliang UNION ALL
SELECT 'a' AS colem,10 AS jiage,2 AS shuliang UNION ALL
SELECT 'a' AS colem,2 AS jiage,2 AS shuliang UNION ALL
SELECT 'e' AS colem,3 AS jiage,6 AS shuliang
) AS T
GROUP BY colem
) AS F
COMPUTE SUM(jiage), SUM(shuliang)
/*
SELECT * INTO ##tt FROM(
SELECT 'q' AS colem,12 AS jiage,1 AS shuliang UNION ALL
SELECT 'a' AS colem,10 AS jiage,2 AS shuliang UNION ALL
SELECT 'a' AS colem,2 AS jiage,2 AS shuliang UNION ALL
SELECT 'e' AS colem,3 AS jiage,6 AS shuliang ) AS a
*/
SELECT * FROM
(
SELECT colem,SUM(jiage) AS jiage,SUM(shuliang) AS shuliang FROM
##tt
GROUP BY colem
) AS F
COMPUTE SUM(jiage), SUM(shuliang)--DROP TABLE ##tt
(
SELECT colem,SUM(jiage) AS jiage,SUM(shuliang) AS shuliang FROM
##tt
GROUP BY colem
) AS F
COMPUTE SUM(jiage), SUM(shuliang)
INSERT @t
( col, jiage, shuliang )
VALUES ( 'q', -- col - char(2)
12, -- jiage - int
1 -- shuliang - int
)
INSERT @t
( col, jiage, shuliang )
VALUES ( 'a', -- col - char(2)
10, -- jiage - int
2 -- shuliang - int
)
INSERT @t
( col, jiage, shuliang )
VALUES ( 'a', -- col - char(2)
2, -- jiage - int
2 -- shuliang - int
)
INSERT @t
( col, jiage, shuliang )
VALUES ( 'e', -- col - char(2)
3, -- jiage - int
6 -- shuliang - int
)SELECT col,SUM(jiage),SUM(shuliang) FROM @t GROUP BY col WITH ROLLUP