有table:
CREATE TABLE Fact_Sale_Profit (
pid number(6),
storeid number(6),
cid number(8),
tdate date,
price number(6),
sale number(8),
profit number(8),
PRIMARY KEY (pid, storeid, cid, tdate, price)
);作两个给定月份累计sale的比较,获得累计sale增加的storeid。应该怎么写sql?
例如:比较2009-10和2009-12的累计销量sum(sale),获得12月较10月销量增加的门店的storeid
注:数据中tdate存贮的是yyyy-mm-dd,而要比较的是yyyy-mmThanks!
CREATE TABLE Fact_Sale_Profit (
pid number(6),
storeid number(6),
cid number(8),
tdate date,
price number(6),
sale number(8),
profit number(8),
PRIMARY KEY (pid, storeid, cid, tdate, price)
);作两个给定月份累计sale的比较,获得累计sale增加的storeid。应该怎么写sql?
例如:比较2009-10和2009-12的累计销量sum(sale),获得12月较10月销量增加的门店的storeid
注:数据中tdate存贮的是yyyy-mm-dd,而要比较的是yyyy-mmThanks!
(select storeid,sum(sal) as sumsal where to_char(tdate,'yyyy-MM')=to_char('2009-12',yyyy-MM')
group by storeid) a,
(select storeid,sum(sal) as sumsal where to_char(tdate,'yyyy-MM')=to_char('2009-10',yyyy-MM')
group by storeid) b
where a.storeid = b.storeid
and a.sumsal > b.sumsal ;
from
(select storeid,sum(sale) as sumsal from Fact_Sale_Profit where to_char(tdate,'yyyy-mm')=to_char('2009-11','yyyy-mm')
group by storeid) a,
(select storeid,sum(sale) as sumsal from Fact_Sale_Profit where to_char(tdate,'yyyy-mm')=to_char('2009-10','yyyy-mm')
group by storeid) b
where a.storeid = b.storeid
and a.sumsal > b.sumsal;报错:
ERROR at line 5: invalid number
看来是因为to_char不能用'yyyy-mm'单独转成年月,而是要'yyyy-mm-dd',我测试好像如果to_date只用'yyyy-mm'是自动使用当月的第一天如2009-10-01谁还有别的办法?
to_date('2009-11-01','yyyy-mm-dd') and to_date('2009-11-30','yyyy-mm-dd')
group by storeid
还有没有更简洁的方法?难道oracle不能单独提取年份或月份吗?
(select storeid,sum(sale) s,trunc(tdate,'MONTH') t from temp group by storeid,trunc(tdate,'MONTH')) k,
(select storeid,sum(sale) s,trunc(tdate,'MONTH') t from temp group by storeid,trunc(tdate,'MONTH')) m
where k.storeid = m.storeid and k.s > m.s and k.t > m.t group by k.storeid order by k.storeid;
from (select storeid,
sum(case when yf = '&Date1' then sale else 0 end) sale1,
sum(case when yf = '&Date2' then sale else 0 end) sale2
from (select storeid,to_char(tdate,'yyyy-mm') yf,sum(sale) sale
from Fact_Sale_Profit
group by storeid,to_char(tdate,'yyyy-mm'))
group by storeid)
where sale1 > sale2;
shit,后面那个to_char不要,
select b.storeid from
(select storeid,sum(sal) as sumsal where to_char(tdate,'yyyy-MM')='2009-12'
group by storeid) a,
(select storeid,sum(sal) as sumsal where to_char(tdate,'yyyy-MM')='2009-10'
group by storeid) b
where a.storeid = b.storeid
and a.sumsal > b.sumsal ;
pid number(6),
storeid number(6),
cid number(8),
tdate date,
price number(6),
sale number(8),
profit number(8),
PRIMARY KEY (pid, storeid, cid, tdate, price)
); select b.storeid from
(select storeid,sum(sale) as sumsal from fact_sale_profit where to_char(tdate,'yyyy-MM')='2009-12'
group by storeid) a,
(select storeid,sum(sale) as sumsal from fact_sale_profit where to_char(tdate,'yyyy-MM')='2009-10'
group by storeid) b
where a.storeid = b.storeid
and a.sumsal > b.sumsal ;