create table hostable( unitname varchar(20),
invcode varchar(20),
nprice varchar(20),
rq varchar(20)
)
#公司名称# #编码# #价格# #定价日期#
insert into hostable values('贸易有限公司', '010101005', '56.13000000', '2016-06-24' );
insert into hostable values('贸易有限公司', '010101005', '58.75000000', '2016-01-27' );
insert into hostable values('贸易有限公司', '010101005', '68.75000000', '2015-03-28' );
insert into hostable values('贸易有限公司', '010101005', '70.00000000', '2014-11-20' );
insert into hostable values('贸易有限公司', '010101005', '77.50000000', '2014-09-07' );
insert into hostable values('贸易有限公司', '010101005', '56.13000000', '2016-05-14' );
insert into hostable values('贸易有限公司', '010101005', '58.75000000', '2016-02-28' );
insert into hostable values('贸易有限公司', '010101005', '68.75000000', '2016-04-11' );
insert into hostable values('贸易有限公司', '010101005', '70.00000000', '2016-05-20' );
insert into hostable values('贸易有限公司', '010101005', '77.50000000', '2016-08-07' );我想查询4月份的价格变动情况,比如我输入查询日期 2016-04-11 ,它的价格是68.75 ,但实际上 整个4月份在2016-02-28---2016-04-11 它的价格是 58.75.因此
因此输入4月份任何一天的查询日期,应该查询出两个价格 一个是 68.75,一个是58.75 ,求助应该怎么办????
invcode varchar(20),
nprice varchar(20),
rq varchar(20)
)
#公司名称# #编码# #价格# #定价日期#
insert into hostable values('贸易有限公司', '010101005', '56.13000000', '2016-06-24' );
insert into hostable values('贸易有限公司', '010101005', '58.75000000', '2016-01-27' );
insert into hostable values('贸易有限公司', '010101005', '68.75000000', '2015-03-28' );
insert into hostable values('贸易有限公司', '010101005', '70.00000000', '2014-11-20' );
insert into hostable values('贸易有限公司', '010101005', '77.50000000', '2014-09-07' );
insert into hostable values('贸易有限公司', '010101005', '56.13000000', '2016-05-14' );
insert into hostable values('贸易有限公司', '010101005', '58.75000000', '2016-02-28' );
insert into hostable values('贸易有限公司', '010101005', '68.75000000', '2016-04-11' );
insert into hostable values('贸易有限公司', '010101005', '70.00000000', '2016-05-20' );
insert into hostable values('贸易有限公司', '010101005', '77.50000000', '2016-08-07' );我想查询4月份的价格变动情况,比如我输入查询日期 2016-04-11 ,它的价格是68.75 ,但实际上 整个4月份在2016-02-28---2016-04-11 它的价格是 58.75.因此
因此输入4月份任何一天的查询日期,应该查询出两个价格 一个是 68.75,一个是58.75 ,求助应该怎么办????
select * from (
select a.*,lag(rq) over (partition by invcode order by rq) p_rq,
lag(nprice) over (partition by invcode order by rq) p_price from hostable a) t
where case when p_rq < '2016-04-01' /* 自己通过输入的日期取月初与月末*/and rq >='2016-04-01' then 1
when p_rq >= '2016-04-01' and p_rq <= '2016-04-30' then 1
else 0
end =1
select * from (
select a.*,lag(rq) over (partition by invcode order by rq) p_rq,
lag(nprice) over (partition by invcode order by rq) p_price from hostable a) t
where case when p_rq < '2016-04-01' /* 自己通过输入的日期取月初与月末*/and rq >'2016-04-01' then 1
when p_rq >= '2016-04-01' and p_rq < '2016-04-30' then 1
else 0
end =1
按这个理解,指定年月的价格可能存在多个(不一定是2个)
with hostable as (
SELECT '贸易有限公司' unitname , '010101005' invcode , '56.13000000' nprice , '2016-06-24' rq FROM DUAL
UNION ALL SELECT '贸易有限公司', '010101005', '58.75000000', '2016-01-27' FROM DUAL
UNION ALL SELECT '贸易有限公司', '010101005', '68.75000000', '2015-03-28' FROM DUAL
UNION ALL SELECT '贸易有限公司', '010101005', '70.00000000', '2014-11-20' FROM DUAL
UNION ALL SELECT '贸易有限公司', '010101005', '77.50000000', '2014-09-07' FROM DUAL
UNION ALL SELECT '贸易有限公司', '010101005', '56.13000000', '2016-05-14' FROM DUAL
UNION ALL SELECT '贸易有限公司', '010101005', '58.75000000', '2016-02-28' FROM DUAL
UNION ALL SELECT '贸易有限公司', '010101005', '68.75000000', '2016-04-11' FROM DUAL
UNION ALL SELECT '贸易有限公司', '010101005', '70.00000000', '2016-05-20' FROM DUAL
UNION ALL SELECT '贸易有限公司', '010101005', '77.50000000', '2016-08-07' FROM DUAL)
select * from (
select a.*,lead(rq) over (partition by invcode order by rq) f_rq from hostable a
) t
where '2016-04-30'>=rq and '2016-04-01'<=f_rq
order by rq;
这里 '2016-04-30'和 '2016-04-01'取指定年月的月末/月初。