• 主页
  • ASP
  • .NET Framework
  • Web Services
  • VB
  • VC
  • 图表区
  • 分析与设计
  • 组件/控件开发
  • LINQ

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
...全文
193 点赞 收藏 6
写回复
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日
坐等山羊过大车
回复 点赞
发动态
发帖子
.NET技术社区
创建于2007-09-28

4.9w+

社区成员

66.8w+

社区内容

.NET技术交流专区
社区公告
暂无公告