create table a(JIHUA int,liangpin int,baofei int) insert into a select 500,400,600 insert into a select 400,100,500create view v_a as select * from a union all select sum(jihua),sum(liangpin),'' from aselect * from v_a JIHUA liangpin baofei 500 400 600 400 100 500 900 500 0
SELECT sum(jihua) FROM ONE其他列的值就查询不到了。 这是为什么?如果这样可以用的话.标准语法怎么写? --- select id,sum(jihua) from table group by id 加了其它列后,当不使用聚合函数时得用group by 子句了
create table a(JIHUA int,liangpin int,baofei int) insert into a select 500,400,600 insert into a select 400,100,500 create view v_a as select * from a1.select jihua,[sum(jihua)]=(select sum(jihua) from v_a) from v_a --结果 jihua sum(jihua) ----------- ----------- 500 900 400 900(2 行受影响)2. select 'hihua',jihua from v_a union all select '总和',sum(jihua) from v_a ---结果 jihua ----- ----------- hihua 500 hihua 400 总和 900
可以对视图使用group by 呀create table t(a int,b int) insert t select 1,1 union all select 2,2 union all select 2,3 union all select 2,4 go create view v as select a,b from t go select a,sum(b) from v group by a drop table t drop view v a ----------- ----------- 1 1 2 9
接上面: select ' ' 项目,a JIHUA,sum(b) LIANGPIN from v group by a union select '合计',sum(a),sum(b) FROM V 项目 JIHUA LIANGPIN ---- ----------- ----------- 1 1 2 9 合计 7 10
500 400 600
400 100 500我要合计JIHUA的值 就是900
LIANGPIN的值合计就是500
谢谢飞天小虫兄,..记得是视图喔
insert into a select 500,400,600
insert into a select 400,100,500create view v_a
as
select * from a
union all
select sum(jihua),sum(liangpin),'' from aselect * from v_a
JIHUA liangpin baofei
500 400 600
400 100 500
900 500 0
---
select id,sum(jihua)
from table
group by id
加了其它列后,当不使用聚合函数时得用group by 子句了
如果用group . SUM就没法用了~不信你试试
我想它查出 jihua 的总合 再把jihua本身的行也查出来.
有办法吗
create table a(JIHUA int,liangpin int,baofei int)
insert into a select 500,400,600
insert into a select 400,100,500
create view v_a
as
select * from a1.select jihua,[sum(jihua)]=(select sum(jihua) from v_a) from v_a
--结果
jihua sum(jihua)
----------- -----------
500 900
400 900(2 行受影响)2.
select 'hihua',jihua from v_a
union all
select '总和',sum(jihua) from v_a
---结果
jihua
----- -----------
hihua 500
hihua 400
总和 900
如果用group . SUM就没法用了~不信你试试
我想它查出 jihua 的总合 再把jihua本身的行也查出来.
有办法吗
---
可以的呀
insert t select 1,1
union all select 2,2
union all select 2,3
union all select 2,4
go
create view v
as
select a,b from t
go
select a,sum(b) from v group by a
drop table t
drop view v
a
----------- -----------
1 1
2 9
select ' ' 项目,a JIHUA,sum(b) LIANGPIN from v group by a
union
select '合计',sum(a),sum(b) FROM V
项目 JIHUA LIANGPIN
---- ----------- -----------
1 1
2 9
合计 7 10