帮助sql server查询。 [英] Help with sql server query.

问题描述

嘿伙计们，我有一个查询，对我来说看起来是messey，我只是想知道是否有人可以为我简化它，因为必须有一种更简洁的方式来返回数据。

< pre lang =SQL> SELECT DestinationName AS [Destination]，cp.RoutingRank，cp.LCRRank < span class =code-keyword> AS [LCR Rank]，cp.LCRRate AS [Carrer Rate]，cp1.LCRRate AS [LCR Rate]，cp2.LCRRate AS [路由速率]，
c1.CarrierShortName AS [LCR Carrier]，c2.CarrierShortName AS [1st Carrier]，cp1.CarrierID AS [LCRCarrierId]，
ISNULL（ovr.CarrierId，ex.CarrierId） AS [覆盖载体]，ISNULL（ovr.ExclusionTypeId， ex.ExclusionTypeId） AS [Exclusion 输入]
FROM dbo.Destination d WITH （ NOLOCK ）
LEFT OUTER JOIN dbo.CarrierPosition cp WITH （ NOLOCK ） ON d.DestinationId = cp.DestinationId


INNER JOIN

- 子查询1
（ SELECT RoutingPlanScheduleId，AnalysisDate，Max（RoutingPlanId） AS RoutingPlanId
< span class =code-keyword> FROM dbo.RoutingPlan WITH （ NOLOCK ）< span class =code-keyword> GROUP BY RoutingPlanScheduleId，AnalysisDate）rp
ON rp.RoutingPlanId = cp.RoutingPlanId


INNER JOIN dbo.RoutingPlanSchedule rps WITH （ NOLOCK ） ON rps.RoutingPlanScheduleId = rp.RoutingPlanScheduleId
INNER JOIN dbo.CarrierPosition cp1 WITH （ NOLOCK ） ON cp1.DestinationId = cp.DestinationId AND cp1.RoutingPlanId = cp.RoutingPlanId AND cp1.LCRRank = 1
INNER JOIN dbo.Carrier c1 WITH （ NOLOCK ） ON c1.CarrierId = cp1.CarrierId
INNER JOIN dbo.CarrierPosition cp2 WITH （ NOLOCK ） ON cp2.DestinationId = cp.DestinationId AND cp2.RoutingPlanId = cp.RoutingPlanId AND cp2.RoutingRank = 1
INNER JOIN dbo.Carrier c2 WITH （ NOLOCK ） ON c2.CarrierId = cp2.CarrierId
LEFT OUTER JOIN


- 子查询2
（ SELECT ISNULL（e.CarrierId，p.CarrierId） AS CarrierId，ExclusionTypeId，DestinationBandId
FROM dbo.ExclusionDestinationBand edb WITH （ NOLOCK ）
INNER JOIN dbo.Exclusion e WITH （ NOLOCK ） ON e.ExclusionId = edb.ExclusionId
INNER JOIN dbo.ExclusionDetail ed WITH （ NOLOCK ） On ed.ExclusionId = e.ExclusionId
LEFT OUTER JOIN dbo.ROProportion p WIT H （ NOLOCK ） ON p.ExclusionDetailId = ed.ExclusionDetailId
WHERE ed.Deleted = 0 AND GetDate（） BETWEEN ed.ActiveFrom AND ed.ActiveTo
AND e.ExclusionTypeId IN （ 3 ， 4 ， 5 ， 6 ））ovr
ON ovr.DestinationBandId = d .DestinationBandId

- 子查询3
LEFT OUTER JOIN
（ SELECT CarrierId，ExclusionTypeId，DestinationBandId
FROM dbo.Exclusion e2 WITH （ NOLOCK ）
INNER JOIN dbo.ExclusionDetail ed2 WITH （ NOLOCK ） On ed2.ExclusionId = e2.ExclusionId
LEFT OUTER JOIN dbo.ExclusionDestinationBand edb2 WITH （ NOLOCK ） ON edb2.ExclusionId = e2.ExclusionId
< span class =code-keyword> WHERE ed2.Deleted = 0 AND GetDate（） BETWEEN ed2.ActiveFrom AND ed2.ActiveTo
AND e2.ExclusionTypeId 不是 IN （ 3 ， 4 ， 5 ， 6 ））ex
ON ex.CarrierId = cp1.CarrierId AND （ex.ExclusionTypeId = 1 OR ex.DestinationBandId = d.DestinationBandId）

- 初始查询的Where子句
WHERE cp.CarrierId = @ CarrierID
AND rps.ROsettingId = @ ROSetting
- AND rps.CommercialSwitch = 0
AND rp .AnalysisDate = @ AnalysisDate
订购 BY DestinationName

谢谢提前大家

解决方案

由于很多原因我们不能为你做这件事...



我建议你阅读这些文章并按照以下步骤操作：

查询优化 [ ^ ]

SQL调优或SQL优化 [ ^ ]

针对TSQL性能的七种罪行[ ^ ] - 我的最爱;）

如何：优化SQL查询 [ ^ ]

Sql教程 - 提高查询性能 [ ^ ]



启动是否足够？

Hey guys, I have a query which to me looks messey, I'm just wondering if someone can simplify it for me as there has to be a neater way to return the data.

SELECT DestinationName AS [Destination], cp.RoutingRank, cp.LCRRank AS [LCR Rank], cp.LCRRate AS [Carrer Rate], cp1.LCRRate AS [LCR Rate], cp2.LCRRate AS [Routing Rate],
c1.CarrierShortName AS [LCR Carrier], c2.CarrierShortName AS [1st Carrier], cp1.CarrierID AS [LCRCarrierId], 
ISNULL(ovr.CarrierId, ex.CarrierId) AS [Override Carrier], ISNULL(ovr.ExclusionTypeId, ex.ExclusionTypeId) AS [Exclusion Type]
FROM dbo.Destination d WITH(NOLOCK)
LEFT OUTER JOIN dbo.CarrierPosition cp WITH(NOLOCK) ON d.DestinationId = cp.DestinationId


INNER JOIN 

--Sub Query 1
(SELECT RoutingPlanScheduleId, AnalysisDate, Max(RoutingPlanId) AS RoutingPlanId 
FROM dbo.RoutingPlan WITH(NOLOCK) GROUP BY RoutingPlanScheduleId, AnalysisDate) rp
ON rp.RoutingPlanId = cp.RoutingPlanId


INNER JOIN dbo.RoutingPlanSchedule rps WITH(NOLOCK) ON rps.RoutingPlanScheduleId = rp.RoutingPlanScheduleId
INNER JOIN dbo.CarrierPosition cp1 WITH(NOLOCK) ON cp1.DestinationId = cp.DestinationId  AND cp1.RoutingPlanId = cp.RoutingPlanId AND cp1.LCRRank = 1
INNER JOIN dbo.Carrier c1 WITH(NOLOCK) ON c1.CarrierId = cp1.CarrierId
INNER JOIN dbo.CarrierPosition cp2 WITH(NOLOCK) ON cp2.DestinationId = cp.DestinationId  AND cp2.RoutingPlanId = cp.RoutingPlanId AND cp2.RoutingRank = 1
INNER JOIN dbo.Carrier c2 WITH(NOLOCK) ON c2.CarrierId = cp2.CarrierId
LEFT OUTER JOIN 


-- Sub Query 2
(SELECT ISNULL(e.CarrierId,p.CarrierId) AS CarrierId, ExclusionTypeId, DestinationBandId
FROM dbo.ExclusionDestinationBand edb WITH(NOLOCK)
INNER JOIN dbo.Exclusion e WITH(NOLOCK) ON e.ExclusionId = edb.ExclusionId
INNER JOIN dbo.ExclusionDetail ed WITH(NOLOCK) On ed.ExclusionId = e.ExclusionId 
LEFT OUTER JOIN dbo.ROProportion p WITH(NOLOCK) ON p.ExclusionDetailId = ed.ExclusionDetailId
WHERE ed.Deleted=0 AND GetDate() BETWEEN ed.ActiveFrom AND ed.ActiveTo
AND e.ExclusionTypeId IN (3,4,5,6)) ovr
ON ovr.DestinationBandId = d.DestinationBandId

-- Sub Query 3
LEFT OUTER JOIN 
(SELECT CarrierId, ExclusionTypeId, DestinationBandId
FROM dbo.Exclusion e2 WITH(NOLOCK)
INNER JOIN dbo.ExclusionDetail ed2 WITH(NOLOCK) On ed2.ExclusionId = e2.ExclusionId 
LEFT OUTER JOIN dbo.ExclusionDestinationBand edb2 WITH(NOLOCK) ON edb2.ExclusionId = e2.ExclusionId
WHERE ed2.Deleted=0 AND GetDate() BETWEEN ed2.ActiveFrom AND ed2.ActiveTo
AND e2.ExclusionTypeId NOT IN (3,4,5,6)) ex 
ON ex.CarrierId  = cp1.CarrierId AND (ex.ExclusionTypeId = 1 OR ex.DestinationBandId = d.DestinationBandId)

--Where clause for initial query
WHERE cp.CarrierId = @CarrierID
AND rps.ROsettingId = @ROSetting
--AND rps.CommercialSwitch = 0
AND rp.AnalysisDate = @AnalysisDate
ORDER BY DestinationName

Thanks in advance guys

解决方案

We can't do that for you because of lot of reasons...

I would suggest you to read these articles and to follow the steps:
Query optimization[^]
SQL Tuning or SQL Optimization[^]
The Seven Sins against TSQL Performance[^] - my favorite ;)
How To: Optimize SQL Queries[^]
Sql tutorial - improving query performance[^]

Is it enough for start?

这篇关于帮助sql server查询。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！