表T1:
ITEMCODE ITEMDES YEAR MONTH PRICE
0101 钢笔 2007 1 10.00
0102 圆珠笔 2007 1 5.00
0101 钢笔 2007 2 11.00
0102 圆珠笔 2007 2 3.00
0101 钢笔 2007 3 9.00
0102 圆珠笔 2007 3 6.00
0101 钢笔 2007 4 15.00
0102 圆珠笔 2007 5 1.00
............
0101 钢笔 2007 11 6.00
0102 圆珠笔 2007 12 3.00现在要从T1中实时生成以下结构,该怎么做?
ITEMCODE ITEMDES YEAR JANUARY FEBRURY MARCH APRIL MAY .... DECEMBER
0101 钢笔 2007 10 11 9 15 ...... 11
0102 圆珠笔 2007 5 3 6 ........... 3
ITEMCODE ITEMDES YEAR MONTH PRICE
0101 钢笔 2007 1 10.00
0102 圆珠笔 2007 1 5.00
0101 钢笔 2007 2 11.00
0102 圆珠笔 2007 2 3.00
0101 钢笔 2007 3 9.00
0102 圆珠笔 2007 3 6.00
0101 钢笔 2007 4 15.00
0102 圆珠笔 2007 5 1.00
............
0101 钢笔 2007 11 6.00
0102 圆珠笔 2007 12 3.00现在要从T1中实时生成以下结构,该怎么做?
ITEMCODE ITEMDES YEAR JANUARY FEBRURY MARCH APRIL MAY .... DECEMBER
0101 钢笔 2007 10 11 9 15 ...... 11
0102 圆珠笔 2007 5 3 6 ........... 3
(select *
from t1
where
month='1') jan,
(select *
from t1
where
month='2') feb,
.
.
.
(select *
from t1
where
month='12') dec,
where jan.ITEMCODE=feb.ITEMCODE,.....nov.ITEMCODE=dec.ITEMCODE
and jan.year=feb.year..............nov.year=dec.year
ITEMCODE,ITEMDES,YEAR,
MAX(DECODE(MONTH, 1,PRICE,0)) as JANUARY,,
MAX(DECODE(MONTH, 2,PRICE,0)) as FEBRURY ,
MAX(DECODE(MONTH, 3,PRICE,0)) as MARCH ,
MAX(DECODE(MONTH, 4,PRICE,0)) as APRIL ,
MAX(DECODE(MONTH, 5,PRICE,0)) as MAY ,
....
MAX(DECODE(MONTH,12,PRICE,0)) as DECEMBER
from
T1
group by
ITEMCODE,ITEMDES,YEAR
(select distnict t1.itemcode,t1.itemdes,t1.year
from t1) total,
(select *
from t1
where
month='1') jan,
(select *
from t1
where
month='2') feb,
.
.
.
(select *
from t1
where
month='12') dec,
where total.ITEMCODE=jan.ITEMCODE(+) and.....total.ITEMCODE=dec.ITEMCODE(+)
and total.year=jan.year(+)..............total.year=dec.year(+)
order by total.ITEMCODE,total.YEAR
的方法不错,比我的简洁多了
---------------------------------------------------------
select
ITEMCODE,ITEMDES,YEAR,
MAX(DECODE(MONTH, 1,PRICE)) as JANUARY ,
MAX(DECODE(MONTH, 2,PRICE)) as FEBRURY ,
MAX(DECODE(MONTH, 3,PRICE)) as MARCH ,
MAX(DECODE(MONTH, 4,PRICE)) as APRIL ,
MAX(DECODE(MONTH, 5,PRICE)) as MAY ,
....
MAX(DECODE(MONTH,12,PRICE)) as DECEMBER
from
T1
group by
ITEMCODE,ITEMDES,YEAR
的方法应该不用MAX和GROUP BY吧
楼主的纪录里,每个商品每个月应该只有一条纪录吧,如果每个商品每个月有多条纪录的话,那也应该用SUM
还想在问一下,如果我一个物品在一个月内有多个记录,要取price的平均值该怎么做?
ITEMCODE,ITEMDES,YEAR,
avg(DECODE(MONTH, 1,PRICE)) as JANUARY ,
avg(DECODE(MONTH, 2,PRICE)) as FEBRURY ,
avg(DECODE(MONTH, 3,PRICE)) as MARCH ,
avg(DECODE(MONTH, 4,PRICE)) as APRIL ,
AVG(DECODE(MONTH, 5,PRICE)) as MAY ,
....
avg(DECODE(MONTH,12,PRICE)) as DECEMBER
from
T1
group by
ITEMCODE,ITEMDES,YEAR
不要告诉我方法是把这个把avg值再乘以12啊
呵呵
select
ITEMCODE,ITEMDES,YEAR,
avg(DECODE(MONTH, 1,PRICE)) as JANUARY ,
avg(DECODE(MONTH, 2,PRICE)) as FEBRURY ,
avg(DECODE(MONTH, 3,PRICE)) as MARCH ,
avg(DECODE(MONTH, 4,PRICE)) as APRIL ,
AVG(DECODE(MONTH, 5,PRICE)) as MAY ,
....
avg(DECODE(MONTH,12,PRICE)) as DECEMBER
from
T1
group by
ITEMCODE,ITEMDES,YEAR用这个算出来的price是:当月的price之和/全年该物品的记录数
CODE,fDES,YEAR,
avg(DECODE(MONTH, 1,PRICE,0)) as JANUARY,
avg(DECODE(MONTH, 2,PRICE,0)) as FEBRURY
from
t_test t1
group by
CODE,fDES,year,month)
group by code,fdes,year