sql 多表写成一个存储过程，根据参数查询存储过程。100大洋求解

耗子哭死猫 2014-01-24 03:05:23

sql比较复杂，建立的视图需要20秒才执行完，20218条数据。现在求一个存储过程，根据时间段 SendTime 参数来查询存储过程，

贴上sql语句

 select  c.DepartmentID,c.FRSerialNumber,c.FaultType,c.SendTime,c.a2,c.eqmethd,c.eqshow,c.eqtype,s.FaultShowID as SfaultShowID,s.FaultShowName,m.DepartmentID as MdepartmentID,m.DepartmentName,

 c.TestResult,c.VerifyNote,c.VerifyTime,c.TestTime,c.EquipmentID,c.EquipmentName,c.RRSerialNumber from

 (

  select b.TestResult,b.VerifyNote,b.VerifyTime,b.TestTime,b.EquipmentID,b.EquipmentName,b.RRSerialNumber,   b.FRSerialNumber,b.eqtype,b.eqshow,b.eqmethd,b.FaultType,b.SendTime,b.DepartmentID, substring(b.eqshow+'|',number,charindex('|',b.eqshow+'|',number+1)-number) a2

 from master..spt_values a,

 (select LEFT(r.FaultType,CHARINDEX('*',r.FaultType)-1)as eqtype,

left(stuff(r.FaultType,1,charindex('*',r.FaultType),''),charindex('*',stuff(r.FaultType,1,charindex('*',r.FaultType),''))-1)as eqshow,

reverse(left(reverse(r.FaultType),charindex('*',reverse(r.FaultType))-1))as eqmethd,

r.SendTime,r.FRSerialNumber,r.DepartmentID,r.TestResult,r.VerifyNote,r.VerifyTime,r.TestTime,r.EquipmentID,r.EquipmentName,r.RRSerialNumber,

r.FaultType from  Posss_RepairReport r 

)as b

 where a.type='p' and substring('|'+b.eqshow,number,1)='|'

 

 )as c left join Posss_EqFaultShow s on c.a2=s.FaultShowID

 left join Posss_Department  m on c.DepartmentID=m.DepartmentID

...全文

278 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

耗子哭死猫 2014-01-26

打赏
举报

引用 5 楼 a01589 的回复:

[quote=引用 2 楼 a01589 的回复:]


if exists (select 1 from sysobjects where name = 'QryInformation' and xtype = 'p')
  drop procedure QryInformation
go
create procedure QryStOutSoOrdMStorage
  ( 
	@SendTime Datetime
  )
  as
  begin
--------------------------------先把最里面的子查询放入临时表#Posss_RepairReport
select LEFT(r.FaultType,CHARINDEX('*',r.FaultType)-1)as eqtype,
left(stuff(r.FaultType,1,charindex('*',r.FaultType),''),
charindex('*',stuff(r.FaultType,1,charindex('*',r.FaultType),''))-1)as eqshow,
reverse(left(reverse(r.FaultType),charindex('*',reverse(r.FaultType))-1))as eqmethd,
r.SendTime,r.FRSerialNumber,r.DepartmentID,r.TestResult,r.VerifyNote,r.VerifyTime,
r.TestTime,r.EquipmentID,r.EquipmentName,r.RRSerialNumber,
r.FaultType
into  #Posss_RepairReport
from  Posss_RepairReport r 
-------------------------------------再从临时表中查出一些数据进行关联  
select  c.*,s.FaultShowID as SfaultShowID,s.FaultShowName,m.DepartmentID as MdepartmentID,m.DepartmentName
 from
 (
  select b.TestResult,b.VerifyNote,b.VerifyTime,b.TestTime,b.EquipmentID,b.EquipmentName,b.RRSerialNumber,  
b.FRSerialNumber,b.eqtype,b.eqshow,b.eqmethd,b.FaultType,b.SendTime,b.DepartmentID, 
substring(b.eqshow+'|',number,charindex('|',b.eqshow+'|',number+1)-number) a2
 from master..spt_values a,----------楼主这句略有不解
 #Posss_RepairReport b
 where a.type='p' and substring('|'+b.eqshow,number,1)='|'
 )as c 
 left join Posss_EqFaultShow s on c.a2=s.FaultShowID
 left join Posss_Department  m on c.DepartmentID=m.DepartmentID
 ------------------------------最后删除临时表
 drop table #Posss_RepairReport
 end

楼主，不好意思，里面有句我写错了，没注意 create procedure QryStOutSoOrdMStorage 应为 create procedure QryInformation 之前我把自己的存储过程名给贴上去了[/quote] 我早改了，不过你这个用临时表速度挺快的。谢谢了，已经解决。

走出一条新道路 2014-01-24

打赏
举报

引用 2 楼 a01589 的回复:


if exists (select 1 from sysobjects where name = 'QryInformation' and xtype = 'p')
  drop procedure QryInformation
go
create procedure QryStOutSoOrdMStorage
  ( 
	@SendTime Datetime
  )
  as
  begin
--------------------------------先把最里面的子查询放入临时表#Posss_RepairReport
select LEFT(r.FaultType,CHARINDEX('*',r.FaultType)-1)as eqtype,
left(stuff(r.FaultType,1,charindex('*',r.FaultType),''),
charindex('*',stuff(r.FaultType,1,charindex('*',r.FaultType),''))-1)as eqshow,
reverse(left(reverse(r.FaultType),charindex('*',reverse(r.FaultType))-1))as eqmethd,
r.SendTime,r.FRSerialNumber,r.DepartmentID,r.TestResult,r.VerifyNote,r.VerifyTime,
r.TestTime,r.EquipmentID,r.EquipmentName,r.RRSerialNumber,
r.FaultType
into  #Posss_RepairReport
from  Posss_RepairReport r 
-------------------------------------再从临时表中查出一些数据进行关联  
select  c.*,s.FaultShowID as SfaultShowID,s.FaultShowName,m.DepartmentID as MdepartmentID,m.DepartmentName
 from
 (
  select b.TestResult,b.VerifyNote,b.VerifyTime,b.TestTime,b.EquipmentID,b.EquipmentName,b.RRSerialNumber,  
b.FRSerialNumber,b.eqtype,b.eqshow,b.eqmethd,b.FaultType,b.SendTime,b.DepartmentID, 
substring(b.eqshow+'|',number,charindex('|',b.eqshow+'|',number+1)-number) a2
 from master..spt_values a,----------楼主这句略有不解
 #Posss_RepairReport b
 where a.type='p' and substring('|'+b.eqshow,number,1)='|'
 )as c 
 left join Posss_EqFaultShow s on c.a2=s.FaultShowID
 left join Posss_Department  m on c.DepartmentID=m.DepartmentID
 ------------------------------最后删除临时表
 drop table #Posss_RepairReport
 end

楼主，不好意思，里面有句我写错了，没注意 create procedure QryStOutSoOrdMStorage 应为 create procedure QryInformation 之前我把自己的存储过程名给贴上去了

H_Gragon 2014-01-24

打赏
举报

引用 2 楼 a01589 的回复:


if exists (select 1 from sysobjects where name = 'QryInformation' and xtype = 'p')
  drop procedure QryInformation
go
create procedure QryStOutSoOrdMStorage
  ( 
	@SendTime Datetime
  )
  as
  begin
--------------------------------先把最里面的子查询放入临时表#Posss_RepairReport
select LEFT(r.FaultType,CHARINDEX('*',r.FaultType)-1)as eqtype,
left(stuff(r.FaultType,1,charindex('*',r.FaultType),''),
charindex('*',stuff(r.FaultType,1,charindex('*',r.FaultType),''))-1)as eqshow,
reverse(left(reverse(r.FaultType),charindex('*',reverse(r.FaultType))-1))as eqmethd,
r.SendTime,r.FRSerialNumber,r.DepartmentID,r.TestResult,r.VerifyNote,r.VerifyTime,
r.TestTime,r.EquipmentID,r.EquipmentName,r.RRSerialNumber,
r.FaultType
into  #Posss_RepairReport
from  Posss_RepairReport r 
-------------------------------------再从临时表中查出一些数据进行关联  
select  c.*,s.FaultShowID as SfaultShowID,s.FaultShowName,m.DepartmentID as MdepartmentID,m.DepartmentName
 from
 (
  select b.TestResult,b.VerifyNote,b.VerifyTime,b.TestTime,b.EquipmentID,b.EquipmentName,b.RRSerialNumber,  
b.FRSerialNumber,b.eqtype,b.eqshow,b.eqmethd,b.FaultType,b.SendTime,b.DepartmentID, 
substring(b.eqshow+'|',number,charindex('|',b.eqshow+'|',number+1)-number) a2
 from master..spt_values a,----------楼主这句略有不解
 #Posss_RepairReport b
 where a.type='p' and substring('|'+b.eqshow,number,1)='|'
 )as c 
 left join Posss_EqFaultShow s on c.a2=s.FaultShowID
 left join Posss_Department  m on c.DepartmentID=m.DepartmentID
 ------------------------------最后删除临时表
 drop table #Posss_RepairReport
 end

楼主可以试试！

走出一条新道路 2014-01-24

打赏
举报

@SendTime Datetime 这个参数看楼主需要放在什么位置

走出一条新道路 2014-01-24

打赏
举报


if exists (select 1 from sysobjects where name = 'QryInformation' and xtype = 'p')
  drop procedure QryInformation
go
create procedure QryStOutSoOrdMStorage
  ( 
	@SendTime Datetime
  )
  as
  begin
--------------------------------先把最里面的子查询放入临时表#Posss_RepairReport
select LEFT(r.FaultType,CHARINDEX('*',r.FaultType)-1)as eqtype,
left(stuff(r.FaultType,1,charindex('*',r.FaultType),''),
charindex('*',stuff(r.FaultType,1,charindex('*',r.FaultType),''))-1)as eqshow,
reverse(left(reverse(r.FaultType),charindex('*',reverse(r.FaultType))-1))as eqmethd,
r.SendTime,r.FRSerialNumber,r.DepartmentID,r.TestResult,r.VerifyNote,r.VerifyTime,
r.TestTime,r.EquipmentID,r.EquipmentName,r.RRSerialNumber,
r.FaultType
into  #Posss_RepairReport
from  Posss_RepairReport r 
-------------------------------------再从临时表中查出一些数据进行关联  
select  c.*,s.FaultShowID as SfaultShowID,s.FaultShowName,m.DepartmentID as MdepartmentID,m.DepartmentName
 from
 (
  select b.TestResult,b.VerifyNote,b.VerifyTime,b.TestTime,b.EquipmentID,b.EquipmentName,b.RRSerialNumber,  
b.FRSerialNumber,b.eqtype,b.eqshow,b.eqmethd,b.FaultType,b.SendTime,b.DepartmentID, 
substring(b.eqshow+'|',number,charindex('|',b.eqshow+'|',number+1)-number) a2
 from master..spt_values a,----------楼主这句略有不解
 #Posss_RepairReport b
 where a.type='p' and substring('|'+b.eqshow,number,1)='|'
 )as c 
 left join Posss_EqFaultShow s on c.a2=s.FaultShowID
 left join Posss_Department  m on c.DepartmentID=m.DepartmentID
 ------------------------------最后删除临时表
 drop table #Posss_RepairReport
 end

耗子哭死猫 2014-01-24