今天写的这个不知道为什么不能用
---------------------------
select 年份2003,
case
when g.年份2003 in (select 年份2003 from (select Ltime,Sid,LNUM,
case
when extract(month from to_date(Ltime,'DD-MON-RR')) in (1,2,3,4) then '季度1'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (5,6,7,8) then '季度2'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (9,10,11,12) then '季度3'
end 年份2003
from saleLog where extract(year from to_date(Ltime,'DD-MON-RR'))=2003) a group by 年份2003) then
(select SNAME from (select rownum as id,a.* from (select sname,sum(LNUM) from saleLog a,Product b where a.sid=b.sid
and extract(year from to_date(Ltime,'DD-MON-RR'))=2003
and extract(month from to_date(Ltime,'DD-MON-RR')) in
(select extract(month from to_date(Ltime,'DD-MON-RR')) from (select Ltime,Sid,LNUM,
case
when extract(month from to_date(Ltime,'DD-MON-RR')) in (1,2,3,4) then '季度1'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (5,6,7,8) then '季度2'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (9,10,11,12) then '季度3'
end 年份2003
from saleLog where extract(year from to_date(Ltime,'DD-MON-RR'))=2003) where 年份2003=g.年份2003)
group by sname order by sum(LNUM) desc) a where rownum<=3) e where id=1)
end 第一名,
case
when g.年份2003 in (select 年份2003 from (select Ltime,Sid,LNUM,
case
when extract(month from to_date(Ltime,'DD-MON-RR')) in (1,2,3,4) then '季度1'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (5,6,7,8) then '季度2'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (9,10,11,12) then '季度3'
end 年份2003
from saleLog where extract(year from to_date(Ltime,'DD-MON-RR'))=2003) a group by 年份2003) then
(select SNAME from (select rownum as id,a.* from (select sname,sum(LNUM) from saleLog a,Product b where a.sid=b.sid
and extract(year from to_date(Ltime,'DD-MON-RR'))=2003
and extract(month from to_date(Ltime,'DD-MON-RR')) in
(select extract(month from to_date(Ltime,'DD-MON-RR')) from (select Ltime,Sid,LNUM,
case
when extract(month from to_date(Ltime,'DD-MON-RR')) in (1,2,3,4) then '季度1'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (5,6,7,8) then '季度2'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (9,10,11,12) then '季度3'
end 年份2003
from saleLog where extract(year from to_date(Ltime,'DD-MON-RR'))=2003) where 年份2003=g.年份2003)
group by sname order by sum(LNUM) desc) a where rownum<=3) e where id=2)
end 第二名,
case
when g.年份2003 in (select 年份2003 from (select Ltime,Sid,LNUM,
case
when extract(month from to_date(Ltime,'DD-MON-RR')) in (1,2,3,4) then '季度1'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (5,6,7,8) then '季度2'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (9,10,11,12) then '季度3'
end 年份2003
from saleLog where extract(year from to_date(Ltime,'DD-MON-RR'))=2003) a group by 年份2003) then
(select SNAME from (select rownum as id,a.* from (select sname,sum(LNUM) from saleLog a,Product b where a.sid=b.sid
and extract(year from to_date(Ltime,'DD-MON-RR'))=2003
and extract(month from to_date(Ltime,'DD-MON-RR')) in
(select extract(month from to_date(Ltime,'DD-MON-RR')) from (select Ltime,Sid,LNUM,
case
when extract(month from to_date(Ltime,'DD-MON-RR')) in (1,2,3,4) then '季度1'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (5,6,7,8) then '季度2'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (9,10,11,12) then '季度3'
end 年份2003
from saleLog where extract(year from to_date(Ltime,'DD-MON-RR'))=2003) where 年份2003=g.年份2003)
group by sname order by sum(LNUM) desc) a where rownum<=3) e where id=3)
end 第三名
from (select Ltime,Sid,LNUM,
case
when extract(month from to_date(Ltime,'DD-MON-RR')) in (1,2,3,4) then '季度1'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (5,6,7,8) then '季度2'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (9,10,11,12) then '季度3'
end 年份2003
from saleLog where extract(year from to_date(Ltime,'DD-MON-RR'))=2003) g group by 年份2003;
--------------------------
报的错是g.年份2003无效求大神解答下
---------------------------
select 年份2003,
case
when g.年份2003 in (select 年份2003 from (select Ltime,Sid,LNUM,
case
when extract(month from to_date(Ltime,'DD-MON-RR')) in (1,2,3,4) then '季度1'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (5,6,7,8) then '季度2'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (9,10,11,12) then '季度3'
end 年份2003
from saleLog where extract(year from to_date(Ltime,'DD-MON-RR'))=2003) a group by 年份2003) then
(select SNAME from (select rownum as id,a.* from (select sname,sum(LNUM) from saleLog a,Product b where a.sid=b.sid
and extract(year from to_date(Ltime,'DD-MON-RR'))=2003
and extract(month from to_date(Ltime,'DD-MON-RR')) in
(select extract(month from to_date(Ltime,'DD-MON-RR')) from (select Ltime,Sid,LNUM,
case
when extract(month from to_date(Ltime,'DD-MON-RR')) in (1,2,3,4) then '季度1'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (5,6,7,8) then '季度2'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (9,10,11,12) then '季度3'
end 年份2003
from saleLog where extract(year from to_date(Ltime,'DD-MON-RR'))=2003) where 年份2003=g.年份2003)
group by sname order by sum(LNUM) desc) a where rownum<=3) e where id=1)
end 第一名,
case
when g.年份2003 in (select 年份2003 from (select Ltime,Sid,LNUM,
case
when extract(month from to_date(Ltime,'DD-MON-RR')) in (1,2,3,4) then '季度1'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (5,6,7,8) then '季度2'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (9,10,11,12) then '季度3'
end 年份2003
from saleLog where extract(year from to_date(Ltime,'DD-MON-RR'))=2003) a group by 年份2003) then
(select SNAME from (select rownum as id,a.* from (select sname,sum(LNUM) from saleLog a,Product b where a.sid=b.sid
and extract(year from to_date(Ltime,'DD-MON-RR'))=2003
and extract(month from to_date(Ltime,'DD-MON-RR')) in
(select extract(month from to_date(Ltime,'DD-MON-RR')) from (select Ltime,Sid,LNUM,
case
when extract(month from to_date(Ltime,'DD-MON-RR')) in (1,2,3,4) then '季度1'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (5,6,7,8) then '季度2'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (9,10,11,12) then '季度3'
end 年份2003
from saleLog where extract(year from to_date(Ltime,'DD-MON-RR'))=2003) where 年份2003=g.年份2003)
group by sname order by sum(LNUM) desc) a where rownum<=3) e where id=2)
end 第二名,
case
when g.年份2003 in (select 年份2003 from (select Ltime,Sid,LNUM,
case
when extract(month from to_date(Ltime,'DD-MON-RR')) in (1,2,3,4) then '季度1'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (5,6,7,8) then '季度2'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (9,10,11,12) then '季度3'
end 年份2003
from saleLog where extract(year from to_date(Ltime,'DD-MON-RR'))=2003) a group by 年份2003) then
(select SNAME from (select rownum as id,a.* from (select sname,sum(LNUM) from saleLog a,Product b where a.sid=b.sid
and extract(year from to_date(Ltime,'DD-MON-RR'))=2003
and extract(month from to_date(Ltime,'DD-MON-RR')) in
(select extract(month from to_date(Ltime,'DD-MON-RR')) from (select Ltime,Sid,LNUM,
case
when extract(month from to_date(Ltime,'DD-MON-RR')) in (1,2,3,4) then '季度1'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (5,6,7,8) then '季度2'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (9,10,11,12) then '季度3'
end 年份2003
from saleLog where extract(year from to_date(Ltime,'DD-MON-RR'))=2003) where 年份2003=g.年份2003)
group by sname order by sum(LNUM) desc) a where rownum<=3) e where id=3)
end 第三名
from (select Ltime,Sid,LNUM,
case
when extract(month from to_date(Ltime,'DD-MON-RR')) in (1,2,3,4) then '季度1'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (5,6,7,8) then '季度2'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (9,10,11,12) then '季度3'
end 年份2003
from saleLog where extract(year from to_date(Ltime,'DD-MON-RR'))=2003) g group by 年份2003;
--------------------------
报的错是g.年份2003无效求大神解答下
select 年份2003, ........ from .... -- 这里是你的那一堆语句
)
select 年份2003 , sum(...)
from m
group by 年份2003
case
when extract(month from to_date(Ltime,'DD-MON-RR')) in (1,2,3,4) then '季度1'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (5,6,7,8) then '季度2'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (9,10,11,12) then '季度3'
end 年份2003
from saleLog where extract(year from to_date(Ltime,'DD-MON-RR'))=2003))
select 年份2003,
case
when ca.年份2003 in (select 季度 from (select Ltime,Sid,LNUM,
case
when extract(month from to_date(Ltime,'DD-MON-RR')) in (1,2,3,4) then '季度1'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (5,6,7,8) then '季度2'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (9,10,11,12) then '季度3'
end 季度
from saleLog where extract(year from to_date(Ltime,'DD-MON-RR'))=2003) a group by 季度) then
(select SNAME from (select rownum as id,a.* from (select sname,sum(LNUM) from saleLog a,Product b where a.sid=b.sid
and extract(year from to_date(Ltime,'DD-MON-RR'))=2003
and extract(month from to_date(Ltime,'DD-MON-RR')) in
(select extract(month from to_date(Ltime,'DD-MON-RR')) from (select Ltime,Sid,LNUM,
case
when extract(month from to_date(Ltime,'DD-MON-RR')) in (1,2,3,4) then '季度1'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (5,6,7,8) then '季度2'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (9,10,11,12) then '季度3'
end 季度
from saleLog where extract(year from to_date(Ltime,'DD-MON-RR'))=2003) where 季度=ca.年份2003)
group by sname order by sum(LNUM) desc) a where rownum<=3) e where id=1)
end 第一名,
case
when ca.年份2003 in (select 季度 from (select Ltime,Sid,LNUM,
case
when extract(month from to_date(Ltime,'DD-MON-RR')) in (1,2,3,4) then '季度1'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (5,6,7,8) then '季度2'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (9,10,11,12) then '季度3'
end 季度
from saleLog where extract(year from to_date(Ltime,'DD-MON-RR'))=2003) a group by 季度) then
(select SNAME from (select rownum as id,a.* from (select sname,sum(LNUM) from saleLog a,Product b where a.sid=b.sid
and extract(year from to_date(Ltime,'DD-MON-RR'))=2003
and extract(month from to_date(Ltime,'DD-MON-RR')) in
(select extract(month from to_date(Ltime,'DD-MON-RR')) from (select Ltime,Sid,LNUM,
case
when extract(month from to_date(Ltime,'DD-MON-RR')) in (1,2,3,4) then '季度1'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (5,6,7,8) then '季度2'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (9,10,11,12) then '季度3'
end 季度
from saleLog where extract(year from to_date(Ltime,'DD-MON-RR'))=2003) where 季度=ca.年份2003)
group by sname order by sum(LNUM) desc) a where rownum<=3) e where id=2)
end 第二名,
case
when ca.年份2003 in (select 季度 from (select Ltime,Sid,LNUM,
case
when extract(month from to_date(Ltime,'DD-MON-RR')) in (1,2,3,4) then '季度1'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (5,6,7,8) then '季度2'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (9,10,11,12) then '季度3'
end 季度
from saleLog where extract(year from to_date(Ltime,'DD-MON-RR'))=2003) a group by 季度) then
(select SNAME from (select rownum as id,a.* from (select sname,sum(LNUM) from saleLog a,Product b where a.sid=b.sid
and extract(year from to_date(Ltime,'DD-MON-RR'))=2003
and extract(month from to_date(Ltime,'DD-MON-RR')) in
(select extract(month from to_date(Ltime,'DD-MON-RR')) from (select Ltime,Sid,LNUM,
case
when extract(month from to_date(Ltime,'DD-MON-RR')) in (1,2,3,4) then '季度1'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (5,6,7,8) then '季度2'
when extract(month from to_date(Ltime,'DD-MON-RR')) in (9,10,11,12) then '季度3'
end 季度
from saleLog where extract(year from to_date(Ltime,'DD-MON-RR'))=2003) where 季度=ca.年份2003)
group by sname order by sum(LNUM) desc) a where rownum<=3) e where id=3)
end 第三名
from ca group by ca.年份2003;
你是说这样子 吧 我试了 还是标识符无效
from ca group by ca.年份2003;ca 是你定义在前面的 with as 别名, 所以 from 前要有 括号;