求大神解答
CREATE or REPLACE view view_REPORTVIEWER_count_type as --创建视图view_REPORTVIEWER_count_type
select y_num_e,m_num_e,d_num_e,m_num_s,y_num_zf,m_num_zf,r_title from
(
--年-完成
select distinct a.r_title,b.rdlc_num y_num_e,null m_num_e,null d_num_e,null m_num_s,null y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'yyyy')=to_char(sysdate,'yyyy'） group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='完成'
union all
--月-完成
select distinct a.r_title,null y_num_e,b.rdlc_num m_num_e,null d_num_e,null m_num_s,null y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'mm')=to_char(sysdate,'mm'） group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='完成'
union all
--日-完成
select distinct a.r_title,null y_num_e,null m_num_e,b.rdlc_num d_num_e,null m_num_s,null y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'dd')=to_char(sysdate,'dd'） group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='完成'
union all
--作业中
select distinct a.r_title,null y_num_e,null m_num_e,null d_num_e,b.rdlc_num m_num_s,null y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'mm')=to_char(sysdate,'mm'） group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='申请'
union all
--年-作废
select distinct a.r_title,null y_num_e,null m_num_e,null d_num_e,null m_num_s,b.rdlc_num y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'yyyy')=to_char(sysdate,'yyyy'） group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='作废'
union all
--月-作废
select distinct a.r_title,null y_num_e,null m_num_e,null d_num_e,null m_num_s,null y_num_zf,b.rdlc_num m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'mm')=to_char(sysdate,'mm'） group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='作废'
)

如上 六条select的联合 不同的只有where内的条件 现在想问问能不能在视图外查询时对视图内的时间where 条件做更改？


即 假如我查询select * from view_REPORTVIEWER_count_type where 年=...and月=...and日=...
那么视图view_REPORTVIEWER_count_type中以年为判断条件的取年的值，以月为判断条件的取月的值，以日为判断条件的取日的值
[解决办法]
你想问的是什么？
[解决办法]

引用:

如：
--存储过程
create procedure REPORTVIEWER_count_type_proc
(@year int,@month int,@daty int)
as
begin
select y_num_e,m_num_e,d_num_e,m_num_s,y_num_zf,m_num_zf,r_title from
(
--年-完成
select distinct a.r_title,b.rdlc_num y_num_e,null m_num_e,null d_num_e,null m_num_s,null y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d

where to_char(d.create_date,'yyyy')=@year group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='完成'
union all
--月-完成
select distinct a.r_title,null y_num_e,b.rdlc_num m_num_e,null d_num_e,null m_num_s,null y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'mm')=@month group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='完成'
union all
--日-完成
select distinct a.r_title,null y_num_e,null m_num_e,b.rdlc_num d_num_e,null m_num_s,null y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'dd')=@daty group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='完成'
union all
--作业中
select distinct a.r_title,null y_num_e,null m_num_e,null d_num_e,b.rdlc_num m_num_s,null y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'mm')=@month group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='申请'
union all
--年-作废
select distinct a.r_title,null y_num_e,null m_num_e,null d_num_e,null m_num_s,b.rdlc_num y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'yyyy')=@year group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='作废'
union all
--月-作废
select distinct a.r_title,null y_num_e,null m_num_e,null d_num_e,null m_num_s,null y_num_zf,b.rdlc_num m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'mm')=@month group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='作废'
) as proc_REPORTVIEWER_count_type
end

你这样写就行的
[解决办法]

版主 存储过程我也写了 你看看
execute REPORTVIEWER_count_type_proc 2013，12，26报错 无效SQL语句
而且存储过程好像不能
select * from REPORTVIEWER_count_type_proc where 年=... and 月=... and 日=...
我最终还是要转成select .....的

你这个语句是在oracle中建的把
[解决办法]

引用:

Quote: 引用:

insert into xxx
exec REPORTVIEWER_count_type_proc 参数
然后操作xxx表

不用insert into xxx是干嘛的再建一个表吗？

嗯，因为存储过程返回一个结果集，但是要找个地方存起来才能操作，那个XXX可以是临时表，