mysql查询语句问题

请问下走过路过的大侠们:
情况:一张表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]是重复的。

1个回答

select field1 t from table
where field2 = 'value' group by field1 order by sum(field3) desc
然后取第一条 sql server是top 1,mysql是limit 1,oracle是 rownum = 1 这个就自己写了
够简单了吧?

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!