oracle SQL 取出每个分组的按照日期最新一条记录，同时还显示每个分组某个字段的总和
code num price date
a 1 10.00 2014-04-01
a 2 20.00 2014-04-02
a 3 30.00 2014-04-03
b 2 20.00 2014-04-07
b 1 10.00 2014-04-03
b 5 22.00 2014-04-01
以code 字段分组，并取每个分组日期最新的一条记录以及每个分组num字段的值的总和
结果：
code num price date SUM(num)
a 3 30.00 2014-04-03 6
b 2 20.00 2014-04-07 8
最新回答
浅夏轻唱
2024-09-17 05:25:01
--用外链接把AA表和CC表链接起来查询就可以了
SELECT AA.CODE, AA.NUM, AA.PRICE, CC.DATETIME, CC.SUMNUM
FROM test AA
--下面的BB表查询的是根据code分组查询num求和及最大datetime，之后作为一个表CC
LEFT JOIN (SELECT BB.CODE, SUM(NUM) SUMNUM,MAX(BB.DATETIME) DATETIME
FROM test BB
GROUP BY BB.CODE) CC
ON AA.CODE = CC.CODE
WHERE AA.DATETIME = CC.DATETIME
--其中DATETIME字段就是你表中的date字段
海不曾怀疑天的蓝
2024-09-17 10:13:31
from (
select code,num,price,date,row_number() over (partition by code order by date desc) rn,
sum(num) over (partition by code) sum_num
from table_name --这一行之前没加,引起误会,抱歉..
) a
where rn=1
不痴情罪名
2024-09-17 05:25:27
select aa.*,bb.sumnum from (select * from test where (code,date) in (select code,max(date) from test group by code)) as aa join (select code,sum(num) as sumnum from test group by code) as bb on aa.code==bb.code
别再相怀
2024-09-17 02:04:51
from test a,(select code,max(date1) date1,sum(num) sum1 from test group by code) b
where a.code=b.code and a.date1=b.date1
;
code和date做为组合关键字必需唯一，否则没法实现你目前的需求
热门标签
