name1 value
------------
student1 1
student2 2
student3 3
查询出来的结果是这样 我想增加一行合计的条件 查询出来结果应该是这样
name1 value
------------
student1 1
student2 2
student3 3
合计 6
这个sql应该怎么写这个是增加一行
如果是这种情况呢?
student1 student2 student3
--------------------------
1 2 3
增加一列查询出的结果
student1 student2 student3 合计
-------------------------------
1 2 3 6
这样的又应该怎么写sql呢?
------------
student1 1
student2 2
student3 3
查询出来的结果是这样 我想增加一行合计的条件 查询出来结果应该是这样
name1 value
------------
student1 1
student2 2
student3 3
合计 6
这个sql应该怎么写这个是增加一行
如果是这种情况呢?
student1 student2 student3
--------------------------
1 2 3
增加一列查询出的结果
student1 student2 student3 合计
-------------------------------
1 2 3 6
这样的又应该怎么写sql呢?
1) union all
2) rollup2.行转列
sum(decode(...))
group by
select name, value from t union all
select '合计' name, sum(value) from t2.
select student1, student2, student3, sum(student1) + sum(student2) + sum(student3) as '合计'
from t
group by student1, student2, student3
2 (
3 select 'student1' name1, 1 value from dual union all
4 select 'student2' name1, 2 value from dual union all
5 select 'student3' name1, 3 value from dual
6 )
7 select case when GROUPING(name1) = 1 then '合计'
8 else name1
9 end name1,
10 sum(value) value
11 from tmp
12 group by rollup(name1)
13 /
NAME1 VALUE
-------- ----------
student1 1
student2 2
student3 3
合计 6
with student as(
select 'student1' name1, 1 value from dual union all
select 'student2' name1, 2 value from dual union all
select 'student3' name1, 3 value from dual)
SELECT *
FROM student
UNION ALL
SELECT '合计', SUM(VALUE) FROM student;--2.方法2
with student as(
select 'student1' name1, 1 value from dual union all
select 'student2' name1, 2 value from dual union all
select 'student3' name1, 3 value from dual)
SELECT decode(GROUPING(name1), 1, '合计', name1), SUM(VALUE)
FROM student
GROUP BY ROLLUP((name1, VALUE));
----------------------------------------------------
select t.* ,student1+student2+student3 "合计" from student t ;