Table_a字段 货号 价格 生效起始日期
a1 12 2010-06-29
a1 5 2009-12-01
b1 50 2010-07-29
b1 25 2010-03-12
b3 45 2007-06-21如何求得各货品最后的生效日期的价格:
货号 价格 生效起始日期
a1 12 2010-06-29
b1 50 2010-07-29
b3 45 2007-06-21如何求得各货物目前的价格:
货号 价格 生效起始日期
a1 12 2010-06-29
b1 25 2010-03-12
b3 45 2007-06-21
谢谢
a1 12 2010-06-29
a1 5 2009-12-01
b1 50 2010-07-29
b1 25 2010-03-12
b3 45 2007-06-21如何求得各货品最后的生效日期的价格:
货号 价格 生效起始日期
a1 12 2010-06-29
b1 50 2010-07-29
b3 45 2007-06-21如何求得各货物目前的价格:
货号 价格 生效起始日期
a1 12 2010-06-29
b1 25 2010-03-12
b3 45 2007-06-21
谢谢
--问题一
with t as (
select 'a1' huohao, '12' jiage, '2010-06-29' riqi from dual union all
select 'a1', '5', '2009-12-01' from dual union all
select 'b1', '50', '2010-07-29' from dual union all
select 'b1', '25', '2010-03-12' from dual union all
select 'b3', '45', '2007-06-21' from dual )
select huohao,jiage,riqi from (
select huohao,jiage, riqi,row_number()
over(partition by huohao order by riqi desc) rn from t )
where rn = 1
--问题二
with t as (
select 'a1' huohao, '12' jiage, '2010-06-29' riqi from dual union all
select 'a1', '5', '2009-12-01' from dual union all
select 'b1', '50', '2010-07-29' from dual union all
select 'b1', '25', '2010-03-12' from dual union all
select 'b3', '45', '2007-06-21' from dual )
select huohao,jiage,riqi from (
select huohao,jiage, riqi,row_number()
over(partition by huohao order by riqi) rn from t )
where rn = 1
select test.*
from test, (select hh, max(EFFECTIVE_DATE) EFFECTIVE_DATE from test group by hh) test2
where test.hh = test2.hh
and test.EFFECTIVE_DATE = test2.EFFECTIVE_DATE;
with t as (
select 'a1' huohao, '12' jiage, '2010-06-29' riqi from dual union all
select 'a1', '5', '2009-12-01' from dual union all
select 'b1', '50', '2010-07-29' from dual union all
select 'b1', '25', '2010-03-12' from dual union all
select 'b3', '45', '2007-06-21' from dual )
select huohao,jiage,riqi from (
select huohao,jiage, riqi,row_number()
over(partition by huohao order by riqi desc) rn from t where riqi < to_char(trunc(sysdate),'yyyy-mm-dd'))
where rn = 1
问题二,,刚才的有错误这个试试
select test.*
from test,
(select hh, max(EFFECTIVE_DATE) EFFECTIVE_DATE
from (select * from test where EFFECTIVE_DATE < sysdate)
group by hh) test2
where test.hh = test2.hh
and test.EFFECTIVE_DATE = test2.EFFECTIVE_DATE;
SQL> create table test(HH varchar2(10), PRICE number(10,2), EFFECTIVE_DATE date);
Table createdSQL> insert into test values( 'a1', 12, to_date('2010-06-29','YYYY-MM-DD'));
1 row inserted
SQL> insert into test values( 'a1', 5, to_date('2009-12-01','YYYY-MM-DD'));
1 row inserted
SQL> insert into test values( 'b1', 50, to_date('2010-07-29','YYYY-MM-DD'));
1 row inserted
SQL> insert into test values( 'b1', 25, to_date('2010-03-12','YYYY-MM-DD'));
1 row inserted
SQL> insert into test values( 'b3', 45, to_date('2007-06-21','YYYY-MM-DD'));
1 row inserted
SQL> commit;SQL> select test.hh, test.price, to_char(test.effective_date, 'YYYY-MM-DD')
2 from test,
3 (select hh, max(EFFECTIVE_DATE) EFFECTIVE_DATE from test group by hh) test2
4 where test.hh = test2.hh
5 and test.EFFECTIVE_DATE = test2.EFFECTIVE_DATE;
HH PRICE TO_CHAR(TEST.EFFECTIVE_DATE,'Y
---------- ------------ ------------------------------
a1 12.00 2010-06-29
b3 45.00 2007-06-21
b1 50.00 2010-07-29
SQL>
SQL> select test.hh, test.price, to_char(test.effective_date, 'YYYY-MM-DD')
2 from test,
3 (select hh, max(EFFECTIVE_DATE) EFFECTIVE_DATE
4 from (select * from test where EFFECTIVE_DATE < sysdate)
5 group by hh) test2
6 where test.hh = test2.hh
7 and test.EFFECTIVE_DATE = test2.EFFECTIVE_DATE;
HH PRICE TO_CHAR(TEST.EFFECTIVE_DATE,'Y
---------- ------------ ------------------------------
a1 12.00 2010-06-29
b1 25.00 2010-03-12
b3 45.00 2007-06-21
SQL>
select 'a1' 货号,12 价格,to_date('2010-06-29','yyyy-mm-dd') 生效起始日期 from dual
union all
select 'a1' 货号,5 价格,to_date('2009-12-01','yyyy-mm-dd') 生效起始日期 from dual
union all
select 'b1' 货号,50 价格,to_date('2010-07-29','yyyy-mm-dd') 生效起始日期 from dual
union all
select 'b1' 货号,25 价格,to_date('2010-03-12','yyyy-mm-dd') 生效起始日期 from dual
union all
select 'b3' 货号,45 价格,to_date('2007-06-21','yyyy-mm-dd') 生效起始日期 from dual
)
select * from(
select 货号,价格,生效起始日期,row_number() over(partition by 货号 order by 货号) rn from temp
) where rn = 1with temp as(
select 'a1' 货号,12 价格,to_date('2010-06-29','yyyy-mm-dd') 生效起始日期 from dual
union all
select 'a1' 货号,5 价格,to_date('2009-12-01','yyyy-mm-dd') 生效起始日期 from dual
union all
select 'b1' 货号,50 价格,to_date('2010-07-29','yyyy-mm-dd') 生效起始日期 from dual
union all
select 'b1' 货号,25 价格,to_date('2010-03-12','yyyy-mm-dd') 生效起始日期 from dual
union all
select 'b3' 货号,45 价格,to_date('2007-06-21','yyyy-mm-dd') 生效起始日期 from dual
)
select * from(
select 货号,价格,生效起始日期,row_number() over(partition by 货号 order by 货号) rn from temp
where 生效起始日期 <= sysdate
) where rn = 1