稍微调整了下楼主的格式。太乱了,容易看不清的。CREATE PROCEDURE dbo.procNewGetQueryPriceByDate2
(
@StartDate CHAR(10),
@EndDate CHAR(10),
@Purpose VARCHAR(20),
@CityId INT = 1
)
AS
SET NOCOUNT ON
if(@Purpose='全部')
Begin
SELECT Q.Id, Q.CId, A.AreaName, Q.CName, Q.BId,
Q.BName, Q.HName, Q.Area, Q.Purpose, Q.CustomerId,
C.OrganName + C.Name AS CustomerName, Q.RegisterPrice,
Q.EvaluationPrice,Q.ReportType,Q.QueryDate,
CASE Q.IsDeliverPrice WHEN 1 THEN 2 END AS ImageIndex,
dbo.GetUserNameById(Creator) AS Creator, CAST(Q.Area * Q.EvaluationPrice AS DECIMAL(18,2)) AS EvaluationAmount,
Q.TaxAmount,Q.UnKnowTax,
CAST(Q.Area * Q.EvaluationPrice - Q.TaxAmount AS DECIMAL(18,2)) AS NetValue, Q.BusinessCase,Q.IncomeCase,Q.DeedCase, Q.EarthCase, Q.AuctionCase,
Q.PoundageCase,Q.Re, Q.IsChangedPrice,
dbo.GetUserNameById(Q.Assessor) as Assessor,
(Case Q.IsFromWeb WHEN 1 THEN 3
WHEN 2 THEN 1
WHEN 0 THEN -1 END) As IsFromWeb,
(CASE Q.EId WHEN 1 THEN '抵押贷款'
WHEN 2 THEN '核定市值'
WHEN 3 THEN '拍卖底价评估' END) as EvaluateType, Q.IsDeliverPrice,R.IsPass, Q.CRe, R.LandLord, Q.NBRe,
Ad.GrantUser, Ad.OldPrice, Ad.NewPrice, (Case When Q.IsFromWeb=0 Then Q.Queristid
When Q.IsFromWeb>0 Then U.username END) as Queryer,
(case IsPrinted when 1 then 1
when 0 then (case InnerPrinted when 1 then 1
when 0 then (select bq.IsPrinted from tbl_bank_queryprice bq
where bq.QueryPriceId=q.QueryPriceId) end)
end) as IsPrinted,
Q.Transactor,'' AS IsOut
FROM QueryPrice Q JOIN Customer C ON Q.CustomerId=C.Id
LEFT JOIN ReportInfo R ON Q.Id=R.QPId
LEFT Join WUPublic.dbo.Construction Co ON Q.CID=Co.ConstructionID
LEFT Join WUPublic.dbo.Region RG ON Co.RegionId= RG.RegionID
LEFT Join WUPublic.dbo.Area A ON RG.AreaId=A.AreaID
LEFT join AdjustPrice Ad on Ad.QPId=Q.Id
LEFT Join BankUsers U on U.[user_id]=Q.Queristid WHERE Q.CityId=@CityId and (Q.QueryDate BETWEEN @StartDate AND @EndDate)
ORDER BY Q.QueryDate DESC
End
else
Begin
SELECT Q.Id, Q.CId, A.AreaName, Q.CName, Q.BId,
Q.BName, Q.HName, Q.Area, Q.Purpose, Q.CustomerId,
C.OrganName + C.Name AS CustomerName, Q.RegisterPrice,Q.EvaluationPrice,
Q.ReportType,Q.QueryDate,
(CASE Q.IsDeliverPrice WHEN 1 THEN 2 END) AS ImageIndex, dbo.GetUserNameById(Creator) AS Creator, CAST(Q.Area * Q.EvaluationPrice AS DECIMAL(18,2)) AS EvaluationAmount,
Q.TaxAmount,Q.UnKnowTax,
CAST(Q.Area * Q.EvaluationPrice - Q.TaxAmount AS DECIMAL(18,2)) AS NetValue,
Q.BusinessCase,Q.IncomeCase,Q.DeedCase,Q.EarthCase, Q.AuctionCase,
Q.PoundageCase,Q.Re,Q.IsChangedPrice, dbo.GetUserNameById(Q.Assessor) as Assessor, (Case Q.IsFromWeb WHEN 1 THEN 3
WHEN 2 THEN 1
WHEN 0 THEN -1 END) As IsFromWeb,
(CASE Q.EId WHEN 1 THEN '抵押贷款'
WHEN 2 THEN '核定市值'
WHEN 3 THEN '拍卖底价评估' END) as EvaluateType,
Q.IsDeliverPrice,R.IsPass,Q.CRe, R.LandLord, Q.NBRe,
Ad.GrantUser, Ad.OldPrice,Ad.NewPrice,
(Case When Q.IsFromWeb=0 Then Q.Queristid
When Q.IsFromWeb>0 Then U.username END) as Queryer,
(case IsPrinted when 1 then 1
when 0 then (case InnerPrinted when 1 then 1
when 0 then (select bq.IsPrinted
from tbl_bank_queryprice bq
where bq.QueryPriceId=q.QueryPriceId) end)
end) AS IsPrinted,
Q.Transactor
FROM QueryPrice Q JOIN Customer C ON Q.CustomerId=C.Id
LEFT JOIN ReportInfo R ON Q.Id=R.QPId
LEFT Join WUPublic.dbo.Construction Co ON Q.CID=Co.ConstructionID
LEFT Join WUPublic.dbo.Region RG ON Co.RegionId= RG.RegionID
LEFT Join WUPublic.dbo.Area A ON RG.AreaId=A.AreaID
LEFT Join AdjustPrice Ad on Ad.QPId=Q.Id
LEFT Join BankUsers U on U.user_id=Q.Queristid
WHERE Q.CityId=@CityId and Q.Purpose=@Purpose and Q.QueryDate BETWEEN @StartDate AND @EndDate
ORDER BY Q.QueryDate DESC
End
SET NOCOUNT OFF
GO
(
@StartDate CHAR(10),
@EndDate CHAR(10),
@Purpose VARCHAR(20),
@CityId INT = 1
)
AS
SET NOCOUNT ON
if(@Purpose='全部')
Begin
SELECT Q.Id, Q.CId, A.AreaName, Q.CName, Q.BId,
Q.BName, Q.HName, Q.Area, Q.Purpose, Q.CustomerId,
C.OrganName + C.Name AS CustomerName, Q.RegisterPrice,
Q.EvaluationPrice,Q.ReportType,Q.QueryDate,
CASE Q.IsDeliverPrice WHEN 1 THEN 2 END AS ImageIndex,
dbo.GetUserNameById(Creator) AS Creator, CAST(Q.Area * Q.EvaluationPrice AS DECIMAL(18,2)) AS EvaluationAmount,
Q.TaxAmount,Q.UnKnowTax,
CAST(Q.Area * Q.EvaluationPrice - Q.TaxAmount AS DECIMAL(18,2)) AS NetValue, Q.BusinessCase,Q.IncomeCase,Q.DeedCase, Q.EarthCase, Q.AuctionCase,
Q.PoundageCase,Q.Re, Q.IsChangedPrice,
dbo.GetUserNameById(Q.Assessor) as Assessor,
(Case Q.IsFromWeb WHEN 1 THEN 3
WHEN 2 THEN 1
WHEN 0 THEN -1 END) As IsFromWeb,
(CASE Q.EId WHEN 1 THEN '抵押贷款'
WHEN 2 THEN '核定市值'
WHEN 3 THEN '拍卖底价评估' END) as EvaluateType, Q.IsDeliverPrice,R.IsPass, Q.CRe, R.LandLord, Q.NBRe,
Ad.GrantUser, Ad.OldPrice, Ad.NewPrice, (Case When Q.IsFromWeb=0 Then Q.Queristid
When Q.IsFromWeb>0 Then U.username END) as Queryer,
(case IsPrinted when 1 then 1
when 0 then (case InnerPrinted when 1 then 1
when 0 then (select bq.IsPrinted from tbl_bank_queryprice bq
where bq.QueryPriceId=q.QueryPriceId) end)
end) as IsPrinted,
Q.Transactor,'' AS IsOut
FROM QueryPrice Q JOIN Customer C ON Q.CustomerId=C.Id
LEFT JOIN ReportInfo R ON Q.Id=R.QPId
LEFT Join WUPublic.dbo.Construction Co ON Q.CID=Co.ConstructionID
LEFT Join WUPublic.dbo.Region RG ON Co.RegionId= RG.RegionID
LEFT Join WUPublic.dbo.Area A ON RG.AreaId=A.AreaID
LEFT join AdjustPrice Ad on Ad.QPId=Q.Id
LEFT Join BankUsers U on U.[user_id]=Q.Queristid WHERE Q.CityId=@CityId and (Q.QueryDate BETWEEN @StartDate AND @EndDate)
ORDER BY Q.QueryDate DESC
End
else
Begin
SELECT Q.Id, Q.CId, A.AreaName, Q.CName, Q.BId,
Q.BName, Q.HName, Q.Area, Q.Purpose, Q.CustomerId,
C.OrganName + C.Name AS CustomerName, Q.RegisterPrice,Q.EvaluationPrice,
Q.ReportType,Q.QueryDate,
(CASE Q.IsDeliverPrice WHEN 1 THEN 2 END) AS ImageIndex, dbo.GetUserNameById(Creator) AS Creator, CAST(Q.Area * Q.EvaluationPrice AS DECIMAL(18,2)) AS EvaluationAmount,
Q.TaxAmount,Q.UnKnowTax,
CAST(Q.Area * Q.EvaluationPrice - Q.TaxAmount AS DECIMAL(18,2)) AS NetValue,
Q.BusinessCase,Q.IncomeCase,Q.DeedCase,Q.EarthCase, Q.AuctionCase,
Q.PoundageCase,Q.Re,Q.IsChangedPrice, dbo.GetUserNameById(Q.Assessor) as Assessor, (Case Q.IsFromWeb WHEN 1 THEN 3
WHEN 2 THEN 1
WHEN 0 THEN -1 END) As IsFromWeb,
(CASE Q.EId WHEN 1 THEN '抵押贷款'
WHEN 2 THEN '核定市值'
WHEN 3 THEN '拍卖底价评估' END) as EvaluateType,
Q.IsDeliverPrice,R.IsPass,Q.CRe, R.LandLord, Q.NBRe,
Ad.GrantUser, Ad.OldPrice,Ad.NewPrice,
(Case When Q.IsFromWeb=0 Then Q.Queristid
When Q.IsFromWeb>0 Then U.username END) as Queryer,
(case IsPrinted when 1 then 1
when 0 then (case InnerPrinted when 1 then 1
when 0 then (select bq.IsPrinted
from tbl_bank_queryprice bq
where bq.QueryPriceId=q.QueryPriceId) end)
end) AS IsPrinted,
Q.Transactor
FROM QueryPrice Q JOIN Customer C ON Q.CustomerId=C.Id
LEFT JOIN ReportInfo R ON Q.Id=R.QPId
LEFT Join WUPublic.dbo.Construction Co ON Q.CID=Co.ConstructionID
LEFT Join WUPublic.dbo.Region RG ON Co.RegionId= RG.RegionID
LEFT Join WUPublic.dbo.Area A ON RG.AreaId=A.AreaID
LEFT Join AdjustPrice Ad on Ad.QPId=Q.Id
LEFT Join BankUsers U on U.user_id=Q.Queristid
WHERE Q.CityId=@CityId and Q.Purpose=@Purpose and Q.QueryDate BETWEEN @StartDate AND @EndDate
ORDER BY Q.QueryDate DESC
End
SET NOCOUNT OFF
GO
1.SQL 语句太复杂了。嵌套太多,函数太多。
2.IF 和ELSE 执行的语句过多重复。优化方式:
1.楼主可以将IF 和ELSE 都用的部分提取出来,建立一视图。
然后对视图进行查询。
2.关键字段建立复合索引。