table1表 有abcdef..等字段按a,b分组。求字段A,字段B,每组C的sum,表中最大的d(这个是表里最大的d,就是说求出来的表这个列都是一样的一个数)这样写前3个都对但是d求出来的是每组最大的 怎么办? select a,b,sum(c),max(d) from table1 group by a,b
select a,b,sum(c),d=(select max(d) from table1) from table1 group by a,b
我开始就这么写的不好使 d=(select max(d) from table1) 没出现在group by里会报错 若是再给他套个max 也不好使
sql server 这样是不报错的
--oracleSQL> create table tb(a int,b int,c int,d int);表已创建。SQL> insert into tb 2 select 1,2,3,4 from dual union all 3 select 1,2,5,8 from dual union all 4 select 1,4,4,6 from dual union all 5 select 1,4,3,10 from dual;已创建4行。SQL> select a,b,sum(c) as c,(select max(d) from tb) as d from tb group by a,b; A B C D ---------- ---------- ---------- ---------- 1 4 7 10 1 2 8 10SQL>
select a,b,sum(c) as c,(select max(d) from tb) as d from tb group by a,b;我在,(select max(d) from tb)这个后面有个where条件 用到了外层from的表 报的错不可以么?要是这样怎么解决?
就是说: select a,b,sum(c) as c,(select max(d) from tb where id=tb2.id) as d from tb1,tb2 where tb1.id=tb2.id group by a,b; 这种情况 不让分组了?
SQL> create table tb1(id int);表已创建。SQL> insert into tb1 select 1 from dual;SQL> select a,b,sum(c) as c,max((select max(d) from tb where a=tb1.id)) as d from tb,tb1 group by a,b; A B C D ---------- ---------- ---------- ---------- 1 4 7 10 1 2 8 10
SQL> update tb1 set id=2;已更新 1 行。SQL> select a,b,sum(c) as c,max((select max(d) from tb where b=tb1.id)) as d from tb,tb1 group by a ,b; A B C D ---------- ---------- ---------- ---------- 1 4 7 8 1 2 8 8
use OA_DB goif exists(select name from sys.tables where name ='tbk') drop table tbk gocreate table tbk(a int,b int,c int, d int); insert into tbk select 1,2,3,4 union all select 1,2,5,8 union all select 1,4,4,6 union all select 1,4,3,10select a,b,sum(c) as c,(select max(d) from tbk at where at.a = ak.a and at.b =ak.b ) as d from tbk ak group by a,b
我开始就这么写的不好使
d=(select max(d) from table1) 没出现在group by里会报错
若是再给他套个max 也不好使
2 select 1,2,3,4 from dual union all
3 select 1,2,5,8 from dual union all
4 select 1,4,4,6 from dual union all
5 select 1,4,3,10 from dual;已创建4行。SQL> select a,b,sum(c) as c,(select max(d) from tb) as d from tb group by a,b; A B C D
---------- ---------- ---------- ----------
1 4 7 10
1 2 8 10SQL>
select a,b,sum(c) as c,(select max(d) from tb) as d from tb group by a,b;我在,(select max(d) from tb)这个后面有个where条件 用到了外层from的表 报的错不可以么?要是这样怎么解决?
select a,b,sum(c) as c,(select max(d) from tb where id=tb2.id) as d from tb1,tb2 where tb1.id=tb2.id group by a,b;
这种情况 不让分组了?
表字段了 就报错了是吧?
---------- ---------- ---------- ----------
1 4 7 10
1 2 8 10
,b; A B C D
---------- ---------- ---------- ----------
1 4 7 8
1 2 8 8
goif exists(select name from sys.tables where name ='tbk')
drop table tbk
gocreate table tbk(a int,b int,c int, d int);
insert into tbk
select 1,2,3,4 union all
select 1,2,5,8 union all
select 1,4,4,6 union all
select 1,4,3,10select a,b,sum(c) as c,(select max(d) from tbk at where at.a = ak.a and at.b =ak.b ) as d from tbk ak group by a,b