表 tb1mtrlName totalPrice Indeptdate
物品1 100 2005-01-05
物品1 200 2005-01-29
物品1 150 2005-08-06
物品2 98 2005-05-18
物品2 101 2005-03-09
.......................结果想要:月份 mtrlName totalPrice
1月 物品1 300
... ... ...
12月 物品1 ...
1月 物品2 ...
... ... ...
12月 物品2 ...能不能实现.用哪种方法实现好.
物品1 100 2005-01-05
物品1 200 2005-01-29
物品1 150 2005-08-06
物品2 98 2005-05-18
物品2 101 2005-03-09
.......................结果想要:月份 mtrlName totalPrice
1月 物品1 300
... ... ...
12月 物品1 ...
1月 物品2 ...
... ... ...
12月 物品2 ...能不能实现.用哪种方法实现好.
group by to_char(Indeptdate,'yyyy-mm'),mtrlName;
----------------------------
一,先建立一表 yearmonthmonthname startdate enddate mtrlName
1月 2007-01-01 2007-01-31 物品1
2月 2007-02-01 2007-02-28 物品2
……
……
12月 2007-01-01 2007-12-31 物品n
二,查询
SELECT yearmonth.monthname,
yearmonth.mtrlName,
SUM(NVL(tb1.totalprice,0)) AS totalprice
FROM yearmonth LEFT JOIN tb1
ON yearmonth.mtrlName = tb1.mtrlName
AND yearmonth.startdate <= tb1.Indeptdate
AND yearmonth.enddate >= tb1.Indeptdate
group by to_char(tb1.Indeptdate,'yyyy'),to_char(tb1.Indeptdate,'mm');