id type time
109 4401 2008-06-01
110 4401 2008-06-02
111 4401 2008-06-03
112 4402 2008-07-01
113 4402 2008-07-11
114 4402 2008-07-12
115 4402 2008-06-11
116 4403 2008-06-01
117 4403 2008-06-09
118 4403 2008-06-08
119 4403 2008-06-01
120 4403 2008-06-01
121 4404 2008-06-02
122 4404 2008-06-01
123 4406 2008-06-06
124 4406 2008-06-05
125 4406 2008-06-04
126 4406 2008-06-03
128 4406 2008-06-01
128 4406 2008-06-02
oracle数据库中有这样一张表,现在我想统计成这样的结果
选择一个时间区间(比如:2008-06-01 --- 2008-06-30),然后计算在这个时间区间的,每周的id个数(count(id))
生成表如下(统计的数据不正确的)
type 20周 21周 22周
4401 1 1 2
4402 2 2 1
其中的周数是通过选择的时间区间自动计算
哪位高手能帮帮忙忙,先谢谢了
109 4401 2008-06-01
110 4401 2008-06-02
111 4401 2008-06-03
112 4402 2008-07-01
113 4402 2008-07-11
114 4402 2008-07-12
115 4402 2008-06-11
116 4403 2008-06-01
117 4403 2008-06-09
118 4403 2008-06-08
119 4403 2008-06-01
120 4403 2008-06-01
121 4404 2008-06-02
122 4404 2008-06-01
123 4406 2008-06-06
124 4406 2008-06-05
125 4406 2008-06-04
126 4406 2008-06-03
128 4406 2008-06-01
128 4406 2008-06-02
oracle数据库中有这样一张表,现在我想统计成这样的结果
选择一个时间区间(比如:2008-06-01 --- 2008-06-30),然后计算在这个时间区间的,每周的id个数(count(id))
生成表如下(统计的数据不正确的)
type 20周 21周 22周
4401 1 1 2
4402 2 2 1
其中的周数是通过选择的时间区间自动计算
哪位高手能帮帮忙忙,先谢谢了
sum(decode(to_char(time,'WW'),'20',1)) as "20周",
sum(decode(to_char(time,'WW'),'21',1)) as "21周",
sum(decode(to_char(time,'WW'),'22',1)) as "22周"
from table1
group by type
-- Author :SQL77(只为思齐老)
-- Date :2010-01-25 23:21:45
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([id] int,[type] int,[time] datetime)
insert #TB
select 109,4401,'2008-06-01' union all
select 110,4401,'2008-06-02' union all
select 111,4401,'2008-06-03' union all
select 112,4402,'2008-07-01' union all
select 113,4402,'2008-07-11' union all
select 114,4402,'2008-07-12' union all
select 115,4402,'2008-06-11' union all
select 116,4403,'2008-06-01' union all
select 117,4403,'2008-06-09' union all
select 118,4403,'2008-06-08' union all
select 119,4403,'2008-06-01' union all
select 120,4403,'2008-06-01' union all
select 121,4404,'2008-06-02' union all
select 122,4404,'2008-06-01' union all
select 123,4406,'2008-06-06' union all
select 124,4406,'2008-06-05' union all
select 125,4406,'2008-06-04' union all
select 126,4406,'2008-06-03' union all
select 128,4406,'2008-06-01' union all
select 128,4406,'2008-06-02'
--------------开始查询--------------------------
DECLARE @S VARCHAR(8000)
SET @S='SELECT TYPE'
SELECT @S=@S+',SUM(CASE WHEN DATEPART(WK,time)='+LTRIM(time)+' THEN 1 ELSE 0 END)
AS '''+LTRIM(TIME)+''''
FROM (SELECT DISTINCT DATEPART(WK,time)time FROM #TB )AS TEXEC(@S+' FROM #TB GROUP BY TYPE')
--select * from #TB
----------------结果----------------------------
/* (所影响的行数为 20 行)TYPE 23 24 27 28
----------- ----------- ----------- ----------- -----------
4401 3 0 0 0
4402 0 1 1 2
4403 3 2 0 0
4404 2 0 0 0
4406 6 0 0 0
*/
http://topic.csdn.net/u/20100109/13/6a10c168-f190-4766-b838-adbf03c4ac7b.html?64786
能提供个详细oracle的存储过程吗?小弟没使用过oracle的存储过程
麻烦各位高手了
create or replace procedure p_test(i_begindate date,
i_enddate date,
o out sys_refcursor) is
v_sqlstr varchar2(4000);
v_decStr varchar2(4000);
begin
if i_enddate>i_begindate then
v_sqlstr := 'select t.type, to_char(time, ''WW'') week, count(*) cnt from mytable t where t.time between date'''||to_char(i_begindate,'yyyy-mm-dd');
v_sqlstr := v_sqlstr||''' and date'''||to_char(i_enddate,'yyyy-mm-dd')||''' group by t.type, to_char(time, ''WW'')';
for i in to_number(to_char(i_begindate,'WW'))..to_number(to_char(i_enddate,'WW')) loop
v_decStr := v_decStr || 'sum(decode(week,'''||lpad(to_char(i),2,'0')||''',cnt,0)) as "'||lpad(to_char(i),2,'0')||'周",';
end loop;
v_sqlstr := 'select type,'||rtrim(v_decstr,',')||' from ('||v_sqlstr ||') group by type';
open o for v_sqlstr;
else
open o for select * from dual where 1=2;
end if;
end p_test;
row_to_col('test123','type','to_char(time,''iw"周"'')','id','count','1','1');
end;
select * from v_tmp;
或
select row_to_col_func('test123','type','to_char(time,''iw"周"'')','id','count','1','1') from dual;