select count(aaa) from tb1_1 where date = sysdate group by aaa;
select count(aaa) from tb1_1 where date bewteen to_date((to_string(sysdate,'yyyy-mm')||'-01'),'yyyy-mm-dd') and to_date((to_string(sysdate,'yyyy-mm')||'-31'),'yyyy-mm-dd') group by aaa;
select count(aaa) from tb1_1 where date bewteen to_date((to_string(sysdate,'yyyy')||'-01-01'),'yyyy-mm-dd') and to_date((to_string(sysdate,'yyyy')||'-12-31'),'yyyy-mm-dd') group by aaa;
select count(aaa) from tb1_1 where date bewteen to_date((to_string(sysdate,'yyyy-mm')||'-01'),'yyyy-mm-dd') and to_date((to_string(sysdate,'yyyy-mm')||'-31'),'yyyy-mm-dd') group by aaa;
select count(aaa) from tb1_1 where date bewteen to_date((to_string(sysdate,'yyyy')||'-01-01'),'yyyy-mm-dd') and to_date((to_string(sysdate,'yyyy')||'-12-31'),'yyyy-mm-dd') group by aaa;
from
(select Col_name,sum(col_int) over(partition by to_char(date,'yyyymmdd')) col_DD,sum(col_int) over(partition by to_char(date,'yyyymm')) col_MM,sum(col_int) over(partition by to_char(date,'yyyy') col_yy)
group by gol_name
请仔细些,我是想用tbl_1生成下边的表.
over\partition 分别是什么意思呀
*******************************************************************
tcmis:?
DECODE是什么涵数呢?写详细些
select gol_name,max(col_DD) col_DD,max(col_MM) col_MM,max(col_yy) col_yy
from
(select Col_name,sum(col_int) over(partition by to_char(date,'yyyymmdd')) col_DD,sum(col_int) over(partition by to_char(date,'yyyymm')) col_MM,sum(col_int) over(partition by to_char(date,'yyyy')) col_yy
from tbl_1)
group by gol_name
http://expert.csdn.net/Expert/topic/2030/2030097.xml?temp=.588833beckhambobo(beckham):
好像这样怎么算也只能算出
COL_NAME COL_DD COL_MM COL_YY
---------- ---------- ---------- ----------
AAA 80 80 150
这样的结果,要是把AAA按照时间[2002/07/02]汇总怎么做,
请教一下over函数,能不能详细一点,谢了.
----------- ---------- ----------
2002-6-1 80 AAA
2002-7-1 40 AAA
2002-7-2 20 AAA
2002-7-2 10 AAA
SQL> select Col_name,sum(col_int) over(partition by to_char(data,'yyyymmdd')) col_DD,sum(col_int) over(partition by to_char(data,'yyyymm')) col_MM,sum(col_int) over(partition by to_char(data,'yyyy')) col_yy
2 from tbl_1
3 ;COL_NAME COL_DD COL_MM COL_YY
---------- ---------- ---------- ----------
AAA 80 80 150
AAA 40 70 150
AAA 30 70 150
AAA 30 70 150SQL> select col_name,min(col_DD) col_DD,min(col_MM) col_MM,min(col_yy) col_yy
2 from
3 (select Col_name,sum(col_int) over(partition by to_char(data,'yyyymmdd')) col_DD,sum(col_int) over(partition by to_char(data,'yyyymm')) col_MM,sum(col_int) over(partition by to_char(data,'yyyy')) col_yy
4 from tbl_1)
5 group by col_name
6 ;COL_NAME COL_DD COL_MM COL_YY
---------- ---------- ---------- ----------
AAA 30 70 150
create function get_dd(p_name in varchar2,p_date in varchar2)
return number
as
cursor t_sor is
select col_int from tbl_1 where name=p_name and to_char(date,'yyyy/mm/dd')=p_date;
num number:=0;
begin
for v_sor in t_sor loop
num:=num+v_sor.col_int;
end loop;
return num;
end;
/
create function get_mm(p_name in varchar2,p_date in varchar2)
return number
as
cursor t_sor is
select col_int from tbl_1 where name=p_name and to_char(date,'yyyy/mm')=p_date;
num number:=0;
begin
for v_sor in t_sor loop
num:=num+v_sor.col_int;
end loop;
return num;
end;
/
create function get_yy(p_name in varchar2,p_date in varchar2)
return number
as
cursor t_sor is
select col_int from tbl_1 where name=p_name and to_char(date,'yyyy')=p_date;
num number:=0;
begin
for v_sor in t_sor loop
num:=num+v_sor.col_int;
end loop;
return num;
end;
/
select col_name,get_dd(col_name,'2002/07/02') col_DD,get_mm(col_name,'2002/07') col_mm,get_yy(col_name,'2002') col_yy from tbl_1 group by col_name