22,209
社区成员
发帖
与我相关
我的任务
分享
create view vw_test
as
select a.sdepotname 使用部门,a.sprogramname 节目名称,a.spersonname 设备预约人,b.typename 设备类型,
round(datediff(n,min(a.dusestarttime),max(a.duseendtime))/60.00,2) as 用时合计
from xc_wjrecord a,xc_wjmasterdevice b
where a.id=b.id and b.typename='D-BET格式摄像机'
group by a.sdepotname,a.sprogramname,a.spersonname,b.typename
union
select a.sdepotname,a.sprogramname,a.spersonname,b.sdevtype as typename,
round(datediff(n,min(a.dusestarttime),max(a.duseendtime))/60.00,2) as ctime from xc_wjrecord a,xc_wjslavedevice b
where a.id=b.id and b.typename='D-BET格式摄像机'
group by a.sdepotname,a.sprogramname,a.spersonname,b.sdevtype
create view vw_test
as
select a.sdepotname 使用部门,a.sprogramname 节目名称,a.spersonname 设备预约人,b.typename 设备类型,
round(datediff(n,min(a.dusestarttime),max(a.duseendtime))/60.00,2) as 用时合计
from xc_wjrecord a,xc_wjmasterdevice b
where a.id=b.id
group by a.sdepotname,a.sprogramname,a.spersonname,b.typename
union
select a.sdepotname,a.sprogramname,a.spersonname,b.sdevtype as typename,
round(datediff(n,min(a.dusestarttime),max(a.duseendtime))/60.00,2) as ctime from xc_wjrecord a,xc_wjslavedevice b
where a.id=b.id
group by a.sdepotname,a.sprogramname,a.spersonname,b.sdevtype
?select * into C from (
select a.sdepotname as 使用部门,a.sprogramname as 节目名称,a.spersonname as 设备预约人,b.typename as 设备类型,
round(datediff(n,min(a.dusestarttime),max(a.duseendtime))/60.00,2) as 用时合计 from xc_wjrecord a,xc_wjmasterdevice b
where a.id=b.id
group by a.sdepotname,a.sprogramname,a.spersonname,b.typename
union
select a.sdepotname,a.sprogramname,a.spersonname,b.sdevtype as typename,
round(datediff(n,min(a.dusestarttime),max(a.duseendtime))/60.00,2) as ctime from xc_wjrecord a,xc_wjslavedevice b
where a.id=b.id
group by a.sdepotname,a.sprogramname,a.spersonname,b.sdevtype
) T
insert into C(使用部门,节目名称,设备预约人,设备类型,用时合计)
Select * from
(select a.sdepotname,a.sprogramname,a.spersonname,b.typename,
round(datediff(n,min(a.dusestarttime),max(a.duseendtime))/60.00,2) as ctime
into c
from xc_wjrecord a,xc_wjmasterdevice b
where a.id=b.id
group by a.sdepotname,a.sprogramname,a.spersonname,b.typename
union
select a.sdepotname,a.sprogramname,a.spersonname,b.sdevtype as typename,
round(datediff(n,min(a.dusestarttime),max(a.duseendtime))/60.00,2) as ctime from xc_wjrecord a,xc_wjslavedevice b
where a.id=b.id
group by a.sdepotname,a.sprogramname,a.spersonname,b.sdevtype) tb