请教SQL语句

Question

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
谢谢

Answer 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 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

Answer 2

问题一:
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;

Answer 3

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
问题二，，刚才的有错误这个试试

Answer 4

问题二:
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;

Answer 5

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> 

Answer 6

with 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 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