有两个表table1和table2
table1 a , b, c
1,2,3
1,2,3
table2 a,b,c
2,3,4
2,3,4
select * from table1
union all
select * from table2
得到
a b c
1 2 3
1 2 3
2 3 4
2 3 4
我想在最后再加上一个合计行
即
a b c
1 2 3
1 2 3
2 3 4
2 3 4
6 10 14
应当怎么样写啊
table1 a , b, c
1,2,3
1,2,3
table2 a,b,c
2,3,4
2,3,4
select * from table1
union all
select * from table2
得到
a b c
1 2 3
1 2 3
2 3 4
2 3 4
我想在最后再加上一个合计行
即
a b c
1 2 3
1 2 3
2 3 4
2 3 4
6 10 14
应当怎么样写啊
a,b,c from tb group by group by
a,b,c
with rollup
select a,b,c from tb group by a,b,c with rollup
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-29 09:29:22
-- 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]([a] int,[b] int,[c] int)
insert [tb]
select 1,2,3 union all
select 1,2,3 union all
select 2,3,4 union all
select 2,3,4
--------------开始查询--------------------------
select sum(a),sum(b),sum(c) from tb group by a,b,c with rollup
----------------结果----------------------------
/* ----------- ----------- -----------
2 4 6
2 4 6
2 4 6
4 6 8
4 6 8
4 6 8
6 10 14(7 行受影响)
*/
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-29 09:29:22
-- 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]([a] int,[b] int,[c] int)
insert [tb]
select 1,2,3 union all
select 1,2,3 union all
select 2,3,4 union all
select 2,3,4
--------------开始查询--------------------------
select * from tb
union all
select sum(a)a,sum(b)b,sum(c)c from tb----------------结果----------------------------
/* a b c
----------- ----------- -----------
1 2 3
1 2 3
2 3 4
2 3 4
6 10 14(5 行受影响)
*/
union all
select * from table2 的表
你可以这样写
;with f as
(select * from table1
union all
select * from table2 )
select * from f
union all
select sum(a)a,sum(b)b,sum(c)c from f
@qy varchar(50),
@yearmonth varchar(50)
)AS
;with f
(select * from table1 union all select * from table 2) select * from f;with f as这个地方老是说有语法错误
union all
select * from table2
union all
select sum(a) , sum(b) , sum(c) from
(
select * from table1
union all
select * from table2
) t
create table table1 (a int ,b int, c int)
insert into table1 values(1,2,3 )
insert into table1 values(1,2,3 )
create table table2 (a int,b int,c int)
insert into table2 values(2,3,4 )
insert into table2 values(2,3,4 )
goselect * from table1
union all
select * from table2
union all
select sum(a) , sum(b) , sum(c) from
(
select * from table1
union all
select * from table2
) tdrop table table1 , table2/*
a b c
----------- ----------- -----------
1 2 3
1 2 3
2 3 4
2 3 4
6 10 14(所影响的行数为 5 行)*/
[/code]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] int,[b] int,[c] int)
insert [tb]
select 1,2,3 union all
select 1,2,3 union all
select 2,3,4 union all
select 2,3,4select * from [tb]
union all
select sum(a) a,sum(b) b,sum(c) c from [tb]
go
create table [tb]([a] int,[b] int,[c] int)
insert [tb]
select 1,2,3 union all
select 1,2,3 union all
select 2,3,4 union all
select 2,3,4
a b c
----------- ----------- -----------
1 2 3
1 2 3
2 3 4
2 3 4
6 10 14
union all
select * from table2
union all
select sum(a),sum(b),sum(c)
from
(
select * from table1
union all
select * from table2
) a
union all
select sum(a)a,sum(b)b,sum(c)c from tb
union all
select * from table2
union all
select sum(a) , sum(b) , sum(c) from
(
select * from table1
union all
select * from table2
) t
--方法2 with rollup