表如下:
Factor 张三 李四
Math 90.00 80.00
History 90.00 89.00
如何增加一行为前两行之和,如下所示:
Factor 张三 李四
Math 90.00 80.00
History 90.00 89.00
SUM 180.00 169.00
Factor 张三 李四
Math 90.00 80.00
History 90.00 89.00
如何增加一行为前两行之和,如下所示:
Factor 张三 李四
Math 90.00 80.00
History 90.00 89.00
SUM 180.00 169.00
union all
select 'SUM',sum(张三), sum(李四)
union all
select 'SUM',SUM(张三),sum(李四) from tb
union all
select 'SUM',sum(张三), sum(李四) from tb
FROM dbo.TB
UNION
SELECT 'SUM',SUM([张三]),SUM([李四])
FROM tb
/*Factor 张三 李四
------- --------------------------------------- ---------------------------------------
History 90.00 89.00
Math 90.00 80.00
SUM 180.00 169.00(3 行受影响)*/
create table tb(Factor varchar(10), 张三 numeric(4,2),李四 numeric(4,2))
insert into tb
select 'Math', 90.00 ,80.00 union all
select 'History', 90.00 ,89.00 select isnull(Factor,'SUM') Factor,sum(张三) 张三,sum(李四) 李四
from tb
group by Factor with rollupFactor 张三 李四
History 90.00 89.00
Math 90.00 80.00
SUM 180.00 169.00
insert @a select 'Math', 90.00, 80.00
union all select 'History', 90.00 ,89.00SELECT ISNULL(Factor,'Sum') Factor,SUM(zs) zs,SUM(ls) ls
FROM @a
GROUP BY Factor
WITH ROLLUP --result
/*
(所影响的行数为 2 行)Factor zs ls
-------------------- ------------------------------ ------------------------------
History 90.00 89.00
Math 90.00 80.00
Sum 180.00 169.00(所影响的行数为 3 行)*/
INSERT INTO @TEMP VALUES ('History', 90.00, 89.00)SELECT ISNULL(Factor,'SUM') AS Factor,SUM([张三])AS [张三],SUM([李四]) AS [李四]
FROM @TEMP
WHERE Factor IN ('Math','History')--过滤条件
GROUP BY Factor
WITH ROLLUP
select isnull(Factor,'SUM') Factor,sum(张三) 张三,sum(李四) 李四
from tb
where Factor in ('Math','History')
group by Factor with rollup
Math 90.00 80.00
History 90.00 89.00
English 80.00 70.00
变成这样
Factor 张三 李四
Math 90.00 80.00
History 90.00 89.00
English 80.00 70.00
SUM(Math+History) 180.00 169.00
Factor 张三 李四
Math 90.00 80.00
History 90.00 89.00
English 80.00 70.00
变成这样
Factor 张三 李四
Math 90.00 80.00
History 90.00 89.00
English 80.00 70.00
SUM(Math+History) 180.00 169.00
这样可以吗》select * from biao
union
select SUM(Math+History),SUM(Math),SUM(History) from biao where Factor in ('Math','History') group by '张三','李四'
union all
select 'SUM',sum(张三), sum(李四)
select * from biao
union
select SUM(Math+History),SUM(Math),SUM(History) from biao where Factor in ('Math','History') group by '张三','李四'
USE TestDB
GO
CREATE TABLE table1
(
Factor varchar(10),
張三 numeric(6,2),
李四 numeric(6,2),
)
GO
INSERT INTO table1 VALUES('MATH',90.00,80.00);
INSERT INTO table1 VALUES('HISTORY',90.00,89.00);
GO
SELECT * FROM table1
UNION ALL
SELECT 'SUM',SUM([張三]),SUM([李四]) FROM table1
GO
--插入第三行数据
DECLARE @zs numeric(6,2)
DECLARE @ls numeric(6,2)
SELECT @zs = SUM([張三]),@ls = SUM([李四]) FROM table1
INSERT INTO table1 VALUES ('SUM',@zs,@ls)
SELECT * FROM table1
GO
DROP TABLE table1不知道是不是你想要的,感觉纯是为了写SQL而写SQL,为什么不将表设计成如下结构呢?USE TestDB
GO
CREATE TABLE SCORE
(
NAME varchar(20),
MATH int,
HISTORY int,
)
GO
INSERT INTO SCORE VALUES('張三',80,90);
INSERT INTO SCORE VALUES('李四',70,95);