1.原始表inoutinfo
2.现在想输入时间范围和操作类型输出对应的结果
2.1创建存储过程
create proc selecttype
@type nvarchar(10),@starttime datetime,@endtime datetime
as
select opdt,opemp,optype,num from inoutinfo where optype=@type and opdt between @starttime and @endtime
go
2.2执行存储过程
exec selecttype '入库','2019-01-18 23:15:25.100','2019-01-18 23:15:25.113'
3.现在想输入时间范围查询所有种类的各自操作的数量
3.1修改存储过程
alter proc selecttype
@starttime datetime,@endtime datetime
as
select optype,sum(num) from inoutinfo where opdt between @starttime and @endtime group by optype
go
3.2执行存储过程
exec selecttype '2019-01-18 23:15:25.100','2019-01-19 23:15:25.113'
4.现在想输入查询类型，时间范围输出明细或汇总
4.1修改存储过程
alter proc selecttype
@selecttype nvarchar(10),@type nvarchar(10),@starttime datetime,@endtime datetime
as
if(@selecttype='汇总')
select optype,sum(num) from inoutinfo where opdt between @starttime and @endtime group by optype
else
select opdt,opemp,optype,num from inoutinfo where optype=@type and opdt between @starttime and @endtime
go
4.2执行存储过程
exec selecttype '','消耗','2019-01-18 01:15:25.100','2019-01-19 13:20:25.113'
exec selecttype '汇总','','2019-01-18 01:15:25.100','2019-01-19 13:20:25.113'
