表 READ
字段 : ID DATE MOUNT
01 1/2/2010 1
01 2/2/2010 2
01 3/2/2010 3
02 1/2/2010 7
02 2/2/2010 8
03 1/2/2010 9
03 2/2/2010 8如上:目的是 求每个ID的date时间离传进参数最近的那条的MOUNT的和 比如我输入参数indate是3/2/2010 那我要的结果就是 (01 3/2/2010 3) (02 2/2/2010 8)
(03 2/2/2010 8) 3+8+8=19;
字段 : ID DATE MOUNT
01 1/2/2010 1
01 2/2/2010 2
01 3/2/2010 3
02 1/2/2010 7
02 2/2/2010 8
03 1/2/2010 9
03 2/2/2010 8如上:目的是 求每个ID的date时间离传进参数最近的那条的MOUNT的和 比如我输入参数indate是3/2/2010 那我要的结果就是 (01 3/2/2010 3) (02 2/2/2010 8)
(03 2/2/2010 8) 3+8+8=19;
解决方案 »
- 创建索引的一个或多个字段上有相同的数据,可以吗?
- 如何设置oracle的并行度
- sql 求平均值问题
- 大家好,我想问一个关于从一张表中提取数据插入另一张表的问题
- truncate table 出错是怎么回事?
- 这样程序错误在那里?
- 用ADO调用一个存储过程来插入一个blob型数据,关于参数类型的问题,请大家来讨论!
- 请教数据检索问题
- 修改IP后数据库为什么启动不了?
- 如何使Windows2000中的Oracle 8.1.6的ManagementServer服务在启动操作系统后自动启动;
- oracle 自定义类型可以用现有表作为其中一个属性的类型吗?
- ORALCE 用IMPDP导入的序列不一致,这是怎么回事?
就是按DATE排序 取第一条
select 1 fid, sysdate-3 as fdate,2 mout from dual
union all
select 1 fid, sysdate-2 fdate,2 mout from dual
union all
select 1 fid, sysdate-1 fdate,2 mout from dual
union all
select 2 fid, sysdate-3 fdate,2 mout from dual
union all
select 2 fid, sysdate-2 fdate,2 mout from dual
)
select sum(mout) from
(
select fid,fdate,mout,row_number() over(partition by fid order by abs(fdate - to_date('&fbegin_date','YYYYMMDD'))) r1
from tmp
) tt
where r1 =1
with temp as
(
select '01' tid, to_date('20100201','yyyymmdd') tdate, 1 tmount from dual
union all
select '01' tid, to_date('20100202','yyyymmdd') tdate, 2 tmount from dual
union all
select '01' tid, to_date('20100203','yyyymmdd') tdate, 3 tmount from dual
union all
select '02' tid, to_date('20100201','yyyymmdd') tdate, 7 tmount from dual
union all
select '02' tid, to_date('20100202','yyyymmdd') tdate, 8 tmount from dual
union all
select '03' tid, to_date('20100201','yyyymmdd') tdate, 9 tmount from dual
union all
select '03' tid, to_date('20100202','yyyymmdd') tdate, 8 tmount from dual
)
select sum(t.tmount)
from temp t, (select tid, min(to_date('20100203','yyyymmdd') - tdate) tmin from temp group by tid) t2
where t.tid = t2.tid
and t2.tmin = to_date('20100203','yyyymmdd') - t.tdate
FROM (
SELECT ID, vdate, MOUNT,
ROW_NUMBER () OVER (PARTITION BY ID ORDER BY ABS
( vdate
- TO_DATE ('03/02/2010',
'DD/MM/YYYY'
)
)) AS rownumber
FROM tab1
) where rownumber = 1
一条语句不行???
根据id分组 取DATE 最大的那条AMOUNT
然后把所有取道的求和
'03/02/2010' 这啥意思?简单的说就是根据id分组 取每个ID DATE 最大的那条AMOUNT 然后全部求和
FROM (SELECT ID, vdate, MOUNT,
ROW_NUMBER () OVER (PARTITION BY ID
ORDER BY ABS( vdate - TO_DATE('03/02/2010','DD/MM/YYYY')), MOUNT DESC) AS rownumber
FROM tab1)
WHERE rownumber = 1
03/02/2010 是你输入的参数indate
其实我想达到的是:根据每个ID分组 抽到DATE最大的那条记录的AMOUNT
然后把所有取到的AMOUNT求和
(
select '01' tid, to_date('20100201','yyyymmdd') tdate, 1 tmount from dual
union all
select '01' tid, to_date('20100202','yyyymmdd') tdate, 2 tmount from dual
union all
select '01' tid, to_date('20100203','yyyymmdd') tdate, 3 tmount from dual
union all
select '02' tid, to_date('20100201','yyyymmdd') tdate, 7 tmount from dual
union all
select '02' tid, to_date('20100202','yyyymmdd') tdate, 8 tmount from dual
union all
select '03' tid, to_date('20100201','yyyymmdd') tdate, 9 tmount from dual
union all
select '03' tid, to_date('20100202','yyyymmdd') tdate, 8 tmount from dual
)
select sum(tmount)
from (select tid,
tdate,
tmount,
row_number() over(partition by tid order by tdate desc) rn
from temp)
where rn = 1
SQL> with temp as
2 (
3 select '01' tid, to_date('20100201','yyyymmdd') tdate, 1 tmount from dual
4 union all
5 select '01' tid, to_date('20100202','yyyymmdd') tdate, 2 tmount from dual
6 union all
7 select '01' tid, to_date('20100203','yyyymmdd') tdate, 3 tmount from dual
8 union all
9 select '02' tid, to_date('20100201','yyyymmdd') tdate, 7 tmount from dual
10 union all
11 select '02' tid, to_date('20100202','yyyymmdd') tdate, 8 tmount from dual
12 union all
13 select '03' tid, to_date('20100201','yyyymmdd') tdate, 9 tmount from dual
14 union all
15 select '03' tid, to_date('20100202','yyyymmdd') tdate, 8 tmount from dual
16 )
17 select sum(tmount)
18 from (select tid,
19 tdate,
20 tmount,
21 row_number() over(partition by tid order by tdate desc) rn
22 from temp)
23 where rn = 1
24 /SUM(TMOUNT)
-----------
19SQL>
2 (
3 select '01' tid, to_date('20100201','yyyymmdd') tdate, 1 tmount from dual
4 union all
5 select '01' tid, to_date('20100202','yyyymmdd') tdate, 2 tmount from dual
6 union all
7 select '01' tid, to_date('20100203','yyyymmdd') tdate, 3 tmount from dual
8 union all
9 select '02' tid, to_date('20100201','yyyymmdd') tdate, 7 tmount from dual
10 union all
11 select '02' tid, to_date('20100202','yyyymmdd') tdate, 8 tmount from dual
12 union all
13 select '03' tid, to_date('20100201','yyyymmdd') tdate, 9 tmount from dual
14 union all
15 select '03' tid, to_date('20100202','yyyymmdd') tdate, 8 tmount from dual
16 )没更简单点的吗???
你这把ID和DATE全给我列出来。那万一我表里有几万条数据,那怎么搞了?
但是必须要用TEMP表吗??? 最好不要有TEMP表阿
你直接去你的数据库里跑这
select sum(tmount)
from (select tid,
tdate,
tmount,
row_number() over(partition by tid order by tdate desc) rn
from temp)
where rn = 1
个就行了,那是个临时表,就相当于你数据库中的实表..只不过临时表是放内存里.实表已经写在表空间里.
执行完SQL也就没有了