请问下走过路过的大侠们:
情况:一张表Table1,字段id,field1,field2,field3.
查询条件&输出结果:
1.field2的值为“value”
2.按field1分组,取出最大sum(field3)对应的field1的值
例子:
field1 field2 field3
1 value 3
1 value 5
2 value 4
2 value 3
2 value 2
根据field1可以分成两组,field1=1的sum(field3)= 3+5=8;field1=2的sum(field3)=4+3+2=9;那么,取最大的sum值,得到的结果是field1=2,即,2.
我写的sql语句很复杂,而且,感觉很烂:
[code="java"]select t3.field1 from Table t3 where t2.field2='value' group by t3.field1 having sum(t3.field3)=(select max(t2.c) from (select t1.field1,t1.field2,sum(t1.field3) c from Table t1 where t1.filed2='value' group by t1.field1) as t2)[/code]
or
[code="java"]select t3.field1 from (select t1.field1,t1.field2,sum(t1.field3) c from Table t1 where t1.field2='value' group by t1.field1) as t3 where t3.c=(select max(t2.c) from (select sum(t2.field3) c from Table t2 where t2.field2='value' group by t2.field1) as t3)[/code]
请问:是否有什么方式简化sql语句或者减少sql查询次数的?
其中,[code="java"]select t1.field1,t1.field2,sum(t1.field3) c from Table t1 where t1.filed2='value' group by t1.field1[/code]是重复的。