- SQL code
select zt_userinfo.[user_name],count(1) as s,sum(case when datediff(mi,zt_flow_log.c_time,zt_flow_log.p_time) <10 then 1 else 0 end) as q,sum(case when datediff(mi,zt_flow_log.c_time,zt_flow_log.p_time) <10 then 0 else 1 end) as dfrom zt_userinfo,zt_flow_logwhere zt_userinfo.login_name = zt_flow_log.p_task_usergroup by zt_userinfo.[user_name]得到的数据大概是这样：user_name s q d名1 4 3 1名2 9 4 5名3 7 3 4得到s（总计），q（在10分钟内的为合格），d（10分钟外为不合格的）现在想得到某个时间段的数据 如：c_time>'2000-1-1' and p_time<'2021-1-1'(时间为变量)现在我要做成视图来操作 我该怎么改
------解决方案--------------------
视图没有参数的
------解决方案--------------------
视图不带参数，做成存储过程不行么？
------解决方案--------------------
视图是不能接收参数的。
你可以把你的SQL 作为一个视图
查询的时候加时间条件
类似这样
- SQL code
create view view_nameasselect zt_userinfo.[user_name],count(1) as s,sum(case when datediff(mi,zt_flow_log.c_time,zt_flow_log.p_time) <10 then 1 else 0 end) as q,sum(case when datediff(mi,zt_flow_log.c_time,zt_flow_log.p_time) <10 then 0 else 1 end) as d,zt_flow_log.p_timefrom zt_userinfo,zt_flow_logwhere zt_userinfo.login_name = zt_flow_log.p_task_usergroup by zt_userinfo.[user_name]---查询时：select user_name,s,q,d from view_name where p_time>'2000-1-1' and p_time<'2021-1-1'
------解决方案--------------------
视图条件都写死的吧？....
做个过程或函数吧...
------解决方案--------------------
- SQL code
CREATE VIEW [dbo].[VW_VIEW1]ASselect [user_name],c_time,p_timefrom zt_userinfo,zt_flow_logwhere zt_userinfo.login_name = zt_flow_log.p_task_userGOselect [user_name],count(1) as s,sum(case when datediff(mi,c_time,p_time) <10 then 1 else 0 end) as q,sum(case when datediff(mi,c_time,p_time) <10 then 0 else 1 end) as dfrom dbo.VW_VIEW1group by [user_name]
------解决方案--------------------
- SQL code
--动态拼接语句执行，你可以给这个语句封装成存储过程declare @str varchar(max)declare @date1 datetimedeclare @date2 datetimeset @date1='2000-1-1'set @date2='2021-1-1'set @str='if object_id('+'''v_test'''+') is not nulldrop view v_testgocreate view v_testasselect zt_userinfo.[user_name],count(1) as s,sum(case when datediff(mi,zt_flow_log.c_time,zt_flow_log.p_time) <10 then 1 else 0 end) as q,sum(case when datediff(mi,zt_flow_log.c_time,zt_flow_log.p_time) <10 then 0 else 1 end) as dfrom zt_userinfo,zt_flow_logwhere zt_userinfo.login_name = zt_flow_log.p_task_userand c_time>'+''''+CONVERT(varchar(10),@date1,120)+''''+' and p_time<'+''''+CONVERT(varchar(10),@date2,120)+''''+'group by zt_userinfo.[user_name]go'print @str/*if object_id('v_test') is not nulldrop view v_testgocreate view v_testasselect zt_userinfo.[user_name],count(1) as s,sum(case when datediff(mi,zt_flow_log.c_time,zt_flow_log.p_time) <10 then 1 else 0 end) as q,sum(case when datediff(mi,zt_flow_log.c_time,zt_flow_log.p_time) <10 then 0 else 1 end) as dfrom zt_userinfo,zt_flow_logwhere zt_userinfo.login_name = zt_flow_log.p_task_userand c_time>'2000-01-01' and p_time<'2021-01-01'group by zt_userinfo.[user_name]go*/