问个算去年同期值的sql

charlesxu 2013-05-21 11:26:05
表中数据如下:
日期 得分
2012-1 85
2012-2 98
2012-3 56
2012-4 66
2012-5 34
。。。
。。。
2013-1 82
2013-2 78
2013-3 29
2013-4 77

sql查4个值,日期、得分、去年同期得分、历史最高得分,希望取到的值如下:

日期 得分 去年同期得分 历史最高得分
2012-1 85
2012-2 98
2012-3 56
2012-4 66
2012-5 34
。。。
。。。
2013-1 82 85 85
2013-2 78 98 98
2013-3 29 56 29
2013-4 66 77 77

请问这个sql怎么写?
...全文
420 点赞 收藏 19
写回复
19 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
run-and-debug 2013-05-22
这样可以查出来:
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

回复
hh7yx 2013-05-21
历史最高得分 是怎么算的?
回复
看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')
回复
hh7yx 2013-05-21
最后面加个where不就可以了么? from t1 where 地区='北京'
回复
charlesxu 2013-05-21
引用 14 楼 u010412956 的回复:
[quote=引用 12 楼 charlesxu 的回复:] [quote=引用 6 楼 u010412956 的回复:] 借下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;
我用你的sql执行时报错:ORA:01427-single-row subquery returns more than one row[/quote] 你相同的年月 会有多条记录,,那就这样:
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;
[/quote] 相同的年月,但是地区不同,能改改吗?我只要北京地区的得分、去年同期得分、历史最高得分 日期 得分 地区 2012-1 85 北京 2012-2 98 天津 2012-3 56 上海 2012-4 66 广州 2012-5 34 深圳
回复
引用 9 楼 charlesxu 的回复:
现在有个问题,我的日期字段是字符型的,格式如下 2011-1 2011-2 。。。 2011-10 2011-12 请问怎么处理才能取出相同的结果?
可以直接截取 因为'-'的位置是固定的 后面最多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
回复
hh7yx 2013-05-21
引用 12 楼 charlesxu 的回复:
[quote=引用 6 楼 u010412956 的回复:] 借下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;
我用你的sql执行时报错:ORA:01427-single-row subquery returns more than one row[/quote] 你相同的年月 会有多条记录,,那就这样:
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;
回复
写多了 第四行去掉lag后面2个参数 lag(sc) over()
回复
charlesxu 2013-05-21
引用 6 楼 u010412956 的回复:
借下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;
我用你的sql执行时报错:ORA:01427-single-row subquery returns more than one row
回复
为什么插入会选择这种格式 转换成日期也不方便 截取也会降低效率..

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
回复
rabitsky 2013-05-21
数据入库的时候控制一下格式不就可以了吗 to_char(XXX,'yyyy-mm')。 或者重新拼接一个to_char(to_date(xx||'-01','yyyy-mm-dd'),'yyyy-mm') …… 这种基本的东西,方法很多
回复
charlesxu 2013-05-21
现在有个问题,我的日期字段是字符型的,格式如下 2011-1 2011-2 。。。 2011-10 2011-12 请问怎么处理才能取出相同的结果?
回复
rabitsky 2013-05-21
借用楼上数据。

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;



回复
vanjayhsu 2013-05-21
引用 6 楼 u010412956 的回复:
借下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;
赞一个
回复
hh7yx 2013-05-21
借下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;
回复
如果是日期格式 里面的截取换成取值 substr(dt,-2) → to_char(dt,'mm') substr(dt,1,4) → to_char(dt,'yyyy')
回复
写麻烦了 不用嵌套 换成子查询就可以了

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
回复
charlesxu 2013-05-21
引用 1 楼 u010412956 的回复:
历史最高得分 是怎么算的?
历史最高得分是之前所有年份当月的最高分,比如2013年7月的历史最高得分是2010、2011、2012年7月得分中最高的。
回复
相关推荐
发帖
Oracle
创建于2007-09-28

1.6w+

社区成员

Oracle开发相关技术讨论
申请成为版主
帖子事件
创建了帖子
2013-05-21 11:26
社区公告
暂无公告