下面是一张表里的数据,我需要按A分组查询,每组中取出max(B),及与B对应的sum(C)
A B C
2007-1-1 1001 100
2007-1-1 1001 100
2007-1-1 1002 200
2007-1-1 1002 200
2007-1-1 1003 300
2007-1-1 1003 300
2007-2-1 2001 100
2007-2-1 2002 200
2007-2-1 2002 200
应该怎么查啊?我没辙了!拜托各位,给个解决办法!
A B C
2007-1-1 1001 100
2007-1-1 1001 100
2007-1-1 1002 200
2007-1-1 1002 200
2007-1-1 1003 300
2007-1-1 1003 300
2007-2-1 2001 100
2007-2-1 2002 200
2007-2-1 2002 200
应该怎么查啊?我没辙了!拜托各位,给个解决办法!
----------------------------------
要看你的A 是什么类型的若是日期型的要这样:select to_char(a,'yyyy-mm-dd'),max(b),sum(c) from test group by to_char(a,'yyyy-mm-dd');不明白的地方:
什么叫做与B对应的SUM(C)呀
where t.to_char(a,'yyyy-mm-dd') = m.m_a and t.b=m.b
group by to_char(a,'yyyy-mm-dd'),b没有测试过 自己测试下
from(
select a,b,c
,max(b)over(partition by a) maxb
,sum(c)over(partition by a,b) sumc
,row_number()over(partition by a,b order by b) rn
from mmtb
)
where b=maxb
and rn=1接分!
A B C
2007-1-1 1001 100
2007-1-1 1001 100
2007-1-1 1002 200
2007-1-1 1002 200
2007-1-1 1003 300
2007-1-1 1003 300
2007-2-1 2001 100
2007-2-1 2002 200
2007-2-1 2002 200 就是说要通过SQL查询出以下的结果
2007-1-1 1003 600
2007-2-1 2002 400
from(
select a,b,c
,max(b)over(partition by a) maxb
,sum(c)over(partition by a,b) sumc
,row_number()over(partition by a,b order by b) rn
from mmtb
)
where b=maxb
and rn=1符合你的需求!
用分析函数 和 用 子查询 哪个效率更高些?