将数据进行周汇总,一天7天 横表转竖表
sql:
select t.year_week,
t.project_type,
t.industry,
t.title,
get_content(#year_week#, 1, t.title) z1,
get_content(#year_week#, 2, t.title) z2,
get_content(#year_week#, 3, t.title) z3,
get_content(#year_week#, 4, t.title) z4,
get_content(#year_week#, 5, t.title) z5,
get_content(#year_week#, 6, t.title) z6,
get_content(#year_week#, 7, t.title) z7,
t.username,
get_week_plan(#year_week#, t.title, t.username) as this_week_plan,
get_week_plan(#year_week# + 1, t.title, t.username) as next_week_plan
from user_v_workbook t
调用oracle写的function 分别获得周信息和日信息
create or replace function get_week_plan(pYearWeek in number,pTitle in varchar2, pUsername in varchar2) return varchar2
--记住:参数和返回值里的数据类型都不用定义长度
is
result varchar2(4000); --定义变量,记住Oracle中定义变量不需要
begin
for temp_cursor in (select a.content from user_workbook A where A.Year_Week = pYearWeek and a.title = pTitle and a.username = pUsername and a.type = 'week') loop
--此处在游标FOR循环中使用查询
result :=result || temp_cursor.content || ',';
--Oracle中字符连接使用||,而sql server中用+
end loop;
result := rtrim(result,',');
--去掉最后一个空格,还有Oracle中的赋值前面没有set
return result;
end;
create or replace function get_content(p_year_week in number,p_week in number,p_title in varchar2)
return varchar2 is
Result varchar2(4000);
begin
Result := '';
for x in (select a.content
from user_workbook a
where a.year_week = p_year_week and a.week = p_week and a.title = p_title and a.type = 'day') loop
Result := Result || to_char(x.content) || '<BR>';
end loop;
return(Result);
end get_content;
关于其他方法,前段时间看了一个封装的很NB的function 但是可惜最后返回的全是int类型,而卧需要string类型