!!!!交叉表的问题!!!!
表 tb1
mtrlName 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 ...
能不能实现.用哪种方法实现好.
[解决办法]
select to_char(Indeptdate, 'yyyy-mm '),mtrlName,sum(totalPrice)
group by to_char(Indeptdate, 'yyyy-mm '),mtrlName;
[解决办法]
兄台,你的问题不是交叉表问题
----------------------------
一,先建立一表 yearmonth
monthname 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 yearmonth.monthname,yearmonth.mtrlName
[解决办法]
select to_char(tb1.Indeptdate, 'mm '),tb1.mtrlName,sum(tb1.totalPrice) from tb1
group by to_char(tb1.Indeptdate, 'yyyy '),to_char(tb1.Indeptdate, 'mm ');
