这效果么 如果日期格式都是固定的 可以 如果是2011-1那可能要找到'-'的位置开始截取了 稍微麻烦了点 with t1 as ( select '2011-01' dt,85 sc from dual union all select '2011-02' dt,98 sc from dual union all select '2011-03' dt,56 sc from dual union all select '2011-04' dt,66 sc from dual union all select '2012-01' dt,33 sc from dual union all select '2012-02' dt,34 sc from dual union all select '2012-03' dt,54 sc from dual union all select '2012-04' dt,69 sc from dual union all select '2013-01' dt,97 sc from dual union all select '2013-02' dt,43 sc from dual union all select '2013-03' dt,44 sc from dual union all select '2013-04' dt,34 sc from dual )select dt,sc,s_sc, (select max(sc) from t1 where t1.dt <= t.dt and substr(t1.dt,-2)=substr(t.dt,-2)) m_sc from ( select dt,sc, case row_number() over(partition by substr(dt,-2) order by rownum) when 1 then null else lag(sc) over(order by substr(dt,-2),substr(dt,1,4)) end s_sc from t1 ) t order by dt dt sc s_sc m_sc ------------------------------------------ 1 2011-01 85 85 2 2011-02 98 98 3 2011-03 56 56 4 2011-04 66 66 5 2012-01 33 85 85 6 2012-02 34 98 98 7 2012-03 54 56 56 8 2012-04 69 66 69 9 2013-01 97 33 97 10 2013-02 43 34 98 11 2013-03 44 54 56 12 2013-04 34 69 69
写麻烦了 不用嵌套 换成子查询就可以了select dt,sc, case row_number() over(partition by substr(dt,-2) order by rownum) when 1 then null else lag(sc) over(order by substr(dt,-2),substr(dt,1,4)) end s_sc, (select max(sc) from t1 where t1.dt <= t.dt and substr(t1.dt,-2)=substr(t.dt,-2)) m_sc from t1 t order by dt
借下2#的数据: with t1 as (select '2011-01' dt, 85 sc from dual union all select '2011-02' dt, 98 sc from dual union all select '2011-03' dt, 56 sc from dual union all select '2011-04' dt, 66 sc from dual union all select '2012-01' dt, 33 sc from dual union all select '2012-02' dt, 34 sc from dual union all select '2012-03' dt, 54 sc from dual union all select '2012-04' dt, 69 sc from dual union all select '2013-01' dt, 97 sc from dual union all select '2013-02' dt, 43 sc from dual union all select '2013-03' dt, 44 sc from dual union all select '2013-04' dt, 34 sc from dual) select t1.dt 日期, t1.sc 得分, (select sc from t1 t where t.dt = to_char(add_months(to_date(t1.dt, 'yyyy-mm'), -12), 'yyyy-mm')) 去年同期得分, (select max(sc) from t1 t where t.dt < t1.dt and substr(t1. dt, 6, 2) = substr(t.dt, 6, 2)) 历史最高得分 from t1;
借用楼上数据。with t1 as ( select '2011-01' dt,85 sc from dual union all select '2011-02' dt,98 sc from dual union all select '2011-03' dt,56 sc from dual union all select '2011-04' dt,66 sc from dual union all select '2012-01' dt,33 sc from dual union all select '2012-02' dt,34 sc from dual union all select '2012-03' dt,54 sc from dual union all select '2012-04' dt,69 sc from dual union all select '2013-01' dt,97 sc from dual union all select '2013-02' dt,43 sc from dual union all select '2013-03' dt,44 sc from dual union all select '2013-04' dt,34 sc from dual ) select dt, sc, lag(sc,1,null) over(partition by substr(dt,-2) order by dt) last_sc, (select max(sc) from t b where b.dt <= a.dt and substr(b.dt,-2) = substr(a.dt,-2)) max_sc from t1 a order by 1;
为什么插入会选择这种格式 转换成日期也不方便 截取也会降低效率.. select dt,sc, case row_number() over(partition by substr(dt,instr(dt,'-')+1,length(dt)) order by rownum) when 1 then null else lag(sc,1,null) over(order by substr(dt,instr(dt,'-')+1,length(dt))) end s_sc, (select max(sc) from t1 where t1.dt <= t.dt and substr(t1.dt,instr(t1.dt,'-')+1,length(t1.dt))=substr(t.dt,instr(t.dt,'-')+1,length(t.dt)) ) m_sc from t1 t order by dt
我用你的sql执行时报错:ORA:01427-single-row subquery returns more than one row
写多了 第四行去掉lag后面2个参数 lag(sc) over()
我用你的sql执行时报错:ORA:01427-single-row subquery returns more than one row你相同的年月 会有多条记录,,那就这样:with t1 as (select '2011-01' dt, 85 sc from dual union all select '2011-02' dt, 98 sc from dual union all select '2011-03' dt, 56 sc from dual union all select '2011-04' dt, 66 sc from dual union all select '2012-01' dt, 33 sc from dual union all select '2012-02' dt, 34 sc from dual union all select '2012-03' dt, 54 sc from dual union all select '2012-04' dt, 69 sc from dual union all select '2013-01' dt, 97 sc from dual union all select '2013-02' dt, 43 sc from dual union all select '2013-03' dt, 44 sc from dual union all select '2013-04' dt, 34 sc from dual) select t1.dt 日期, t1.sc 得分, (select sum(sc) from t1 t where t.dt = to_char(add_months(to_date(t1.dt, 'yyyy-mm'), -12), 'yyyy-mm')) 去年同期得分, (select max(sc) from t1 t where t.dt < t1.dt and substr(t1. dt, 6, 2) = substr(t.dt, 6, 2)) 历史最高得分 from t1;
可以直接截取 因为'-'的位置是固定的 后面最多2位或者to_char(to_date(dt,'yyyy-mm'),'mm')就是麻烦点 效率也不行 select dt,sc, case row_number() over(partition by substr(dt,6,2) order by rownum) when 1 then null else lag(sc) over(order by substr(dt,6,2)) end s_sc, (select max(sc) from t1 where t1.dt <= t.dt and substr(t1.dt,6,2)=substr(t.dt,6,2) ) m_sc from t1 t order by dt
我用你的sql执行时报错:ORA:01427-single-row subquery returns more than one row你相同的年月 会有多条记录,,那就这样:with t1 as (select '2011-01' dt, 85 sc from dual union all select '2011-02' dt, 98 sc from dual union all select '2011-03' dt, 56 sc from dual union all select '2011-04' dt, 66 sc from dual union all select '2012-01' dt, 33 sc from dual union all select '2012-02' dt, 34 sc from dual union all select '2012-03' dt, 54 sc from dual union all select '2012-04' dt, 69 sc from dual union all select '2013-01' dt, 97 sc from dual union all select '2013-02' dt, 43 sc from dual union all select '2013-03' dt, 44 sc from dual union all select '2013-04' dt, 34 sc from dual) select t1.dt 日期, t1.sc 得分, (select sum(sc) from t1 t where t.dt = to_char(add_months(to_date(t1.dt, 'yyyy-mm'), -12), 'yyyy-mm')) 去年同期得分, (select max(sc) from t1 t where t.dt < t1.dt and substr(t1. dt, 6, 2) = substr(t.dt, 6, 2)) 历史最高得分 from t1;相同的年月,但是地区不同,能改改吗?我只要北京地区的得分、去年同期得分、历史最高得分 日期 得分 地区 2012-1 85 北京 2012-2 98 天津 2012-3 56 上海 2012-4 66 广州 2012-5 34 深圳
最后面加个where不就可以了么? from t1 where 地区='北京'
看lz也发了200多个贴了 基本的sql应该清楚吧 where过滤地区然后要用上面那sql的话 可能这里要改改 转换后的月份是有0的(select sum(sc) from t1 t where t.dt = to_char(add_months(to_date(t1.dt, 'yyyy-mm'), -12), 'yyyy-mm')) 去年同期得分 where t.dt —→ where to_char(to_date(t.dt,'yyyy-mm'),'yyyy-mm') 或者where substr(t.dt,1,5)||lpad(substr(t.dt,6,2),2,'0')
这样可以查出来: SELECT t1.dt,t1.score, (SELECT t2.score FROM VALUE t2 WHERE SUBSTR(t2.dt,6,2)= SUBSTR(t1.dt,6,2) AND SUBSTR(t2.dt,1,4)= (SUBSTR(t1.dt,1,4)-1) ) AS lastYear, (SELECT MAX(t3.score) FROM VALUE t3 WHERE SUBSTR(t3.dt,6,2)= SUBSTR(t1.dt,6,2) AND SUBSTR(t3.dt,1,4) < SUBSTR(t1.dt,1,4) ) AS lastYear1 FROM VALUE t1
如果日期格式都是固定的 可以
如果是2011-1那可能要找到'-'的位置开始截取了 稍微麻烦了点
with t1 as
(
select '2011-01' dt,85 sc from dual union all
select '2011-02' dt,98 sc from dual union all
select '2011-03' dt,56 sc from dual union all
select '2011-04' dt,66 sc from dual union all
select '2012-01' dt,33 sc from dual union all
select '2012-02' dt,34 sc from dual union all
select '2012-03' dt,54 sc from dual union all
select '2012-04' dt,69 sc from dual union all
select '2013-01' dt,97 sc from dual union all
select '2013-02' dt,43 sc from dual union all
select '2013-03' dt,44 sc from dual union all
select '2013-04' dt,34 sc from dual
)select dt,sc,s_sc,
(select max(sc) from t1 where t1.dt <= t.dt and substr(t1.dt,-2)=substr(t.dt,-2)) m_sc
from
(
select dt,sc,
case row_number() over(partition by substr(dt,-2) order by rownum)
when 1 then null
else lag(sc) over(order by substr(dt,-2),substr(dt,1,4)) end s_sc
from t1
) t
order by dt dt sc s_sc m_sc
------------------------------------------
1 2011-01 85 85
2 2011-02 98 98
3 2011-03 56 56
4 2011-04 66 66
5 2012-01 33 85 85
6 2012-02 34 98 98
7 2012-03 54 56 56
8 2012-04 69 66 69
9 2013-01 97 33 97
10 2013-02 43 34 98
11 2013-03 44 54 56
12 2013-04 34 69 69
case row_number() over(partition by substr(dt,-2) order by rownum)
when 1 then null
else lag(sc) over(order by substr(dt,-2),substr(dt,1,4)) end s_sc,
(select max(sc) from t1 where t1.dt <= t.dt and substr(t1.dt,-2)=substr(t.dt,-2)) m_sc
from t1 t
order by dt
substr(dt,1,4) → to_char(dt,'yyyy')
with t1 as
(select '2011-01' dt, 85 sc
from dual
union all
select '2011-02' dt, 98 sc
from dual
union all
select '2011-03' dt, 56 sc
from dual
union all
select '2011-04' dt, 66 sc
from dual
union all
select '2012-01' dt, 33 sc
from dual
union all
select '2012-02' dt, 34 sc
from dual
union all
select '2012-03' dt, 54 sc
from dual
union all
select '2012-04' dt, 69 sc
from dual
union all
select '2013-01' dt, 97 sc
from dual
union all
select '2013-02' dt, 43 sc
from dual
union all
select '2013-03' dt, 44 sc
from dual
union all
select '2013-04' dt, 34 sc from dual)
select t1.dt 日期,
t1.sc 得分,
(select sc
from t1 t
where t.dt =
to_char(add_months(to_date(t1.dt, 'yyyy-mm'), -12), 'yyyy-mm')) 去年同期得分,
(select max(sc)
from t1 t
where t.dt < t1.dt
and substr(t1. dt, 6, 2) = substr(t.dt, 6, 2)) 历史最高得分
from t1;
select '2011-01' dt,85 sc from dual union all
select '2011-02' dt,98 sc from dual union all
select '2011-03' dt,56 sc from dual union all
select '2011-04' dt,66 sc from dual union all
select '2012-01' dt,33 sc from dual union all
select '2012-02' dt,34 sc from dual union all
select '2012-03' dt,54 sc from dual union all
select '2012-04' dt,69 sc from dual union all
select '2013-01' dt,97 sc from dual union all
select '2013-02' dt,43 sc from dual union all
select '2013-03' dt,44 sc from dual union all
select '2013-04' dt,34 sc from dual )
select dt, sc, lag(sc,1,null) over(partition by substr(dt,-2) order by dt) last_sc,
(select max(sc)
from t b
where b.dt <= a.dt
and substr(b.dt,-2) = substr(a.dt,-2)) max_sc
from t1 a
order by 1;
或者重新拼接一个to_char(to_date(xx||'-01','yyyy-mm-dd'),'yyyy-mm')
……
这种基本的东西,方法很多
select dt,sc,
case row_number() over(partition by substr(dt,instr(dt,'-')+1,length(dt)) order by rownum)
when 1 then null
else lag(sc,1,null) over(order by substr(dt,instr(dt,'-')+1,length(dt))) end s_sc,
(select max(sc)
from t1
where t1.dt <= t.dt
and substr(t1.dt,instr(t1.dt,'-')+1,length(t1.dt))=substr(t.dt,instr(t.dt,'-')+1,length(t.dt))
) m_sc
from t1 t
order by dt
我用你的sql执行时报错:ORA:01427-single-row subquery returns more than one row
我用你的sql执行时报错:ORA:01427-single-row subquery returns more than one row你相同的年月 会有多条记录,,那就这样:with t1 as
(select '2011-01' dt, 85 sc
from dual
union all
select '2011-02' dt, 98 sc
from dual
union all
select '2011-03' dt, 56 sc
from dual
union all
select '2011-04' dt, 66 sc
from dual
union all
select '2012-01' dt, 33 sc
from dual
union all
select '2012-02' dt, 34 sc
from dual
union all
select '2012-03' dt, 54 sc
from dual
union all
select '2012-04' dt, 69 sc
from dual
union all
select '2013-01' dt, 97 sc
from dual
union all
select '2013-02' dt, 43 sc
from dual
union all
select '2013-03' dt, 44 sc
from dual
union all
select '2013-04' dt, 34 sc from dual)
select t1.dt 日期,
t1.sc 得分,
(select sum(sc)
from t1 t
where t.dt =
to_char(add_months(to_date(t1.dt, 'yyyy-mm'), -12), 'yyyy-mm')) 去年同期得分,
(select max(sc)
from t1 t
where t.dt < t1.dt
and substr(t1. dt, 6, 2) = substr(t.dt, 6, 2)) 历史最高得分
from t1;
select dt,sc,
case row_number() over(partition by substr(dt,6,2) order by rownum)
when 1 then null
else lag(sc) over(order by substr(dt,6,2)) end s_sc,
(select max(sc)
from t1
where t1.dt <= t.dt
and substr(t1.dt,6,2)=substr(t.dt,6,2)
) m_sc
from t1 t
order by dt
我用你的sql执行时报错:ORA:01427-single-row subquery returns more than one row你相同的年月 会有多条记录,,那就这样:with t1 as
(select '2011-01' dt, 85 sc
from dual
union all
select '2011-02' dt, 98 sc
from dual
union all
select '2011-03' dt, 56 sc
from dual
union all
select '2011-04' dt, 66 sc
from dual
union all
select '2012-01' dt, 33 sc
from dual
union all
select '2012-02' dt, 34 sc
from dual
union all
select '2012-03' dt, 54 sc
from dual
union all
select '2012-04' dt, 69 sc
from dual
union all
select '2013-01' dt, 97 sc
from dual
union all
select '2013-02' dt, 43 sc
from dual
union all
select '2013-03' dt, 44 sc
from dual
union all
select '2013-04' dt, 34 sc from dual)
select t1.dt 日期,
t1.sc 得分,
(select sum(sc)
from t1 t
where t.dt =
to_char(add_months(to_date(t1.dt, 'yyyy-mm'), -12), 'yyyy-mm')) 去年同期得分,
(select max(sc)
from t1 t
where t.dt < t1.dt
and substr(t1. dt, 6, 2) = substr(t.dt, 6, 2)) 历史最高得分
from t1;相同的年月,但是地区不同,能改改吗?我只要北京地区的得分、去年同期得分、历史最高得分 日期 得分 地区
2012-1 85 北京
2012-2 98 天津
2012-3 56 上海
2012-4 66 广州
2012-5 34 深圳
from t1
where 地区='北京'
from t1 t
where t.dt =
to_char(add_months(to_date(t1.dt, 'yyyy-mm'), -12), 'yyyy-mm')) 去年同期得分
where t.dt —→ where to_char(to_date(t.dt,'yyyy-mm'),'yyyy-mm')
或者where substr(t.dt,1,5)||lpad(substr(t.dt,6,2),2,'0')
SELECT t1.dt,t1.score,
(SELECT t2.score FROM VALUE t2 WHERE SUBSTR(t2.dt,6,2)= SUBSTR(t1.dt,6,2)
AND SUBSTR(t2.dt,1,4)= (SUBSTR(t1.dt,1,4)-1)
) AS lastYear,
(SELECT MAX(t3.score) FROM VALUE t3 WHERE SUBSTR(t3.dt,6,2)= SUBSTR(t1.dt,6,2)
AND SUBSTR(t3.dt,1,4) < SUBSTR(t1.dt,1,4)
) AS lastYear1
FROM VALUE t1