DECLARE @IRoutingID varchar(8000)
DECLARE @TRouting table( RoutingID UNIQUEIDENTIFIER)
select @IRoutingID='select '''+replace(@RoutingID,',',''' union all select ''')+''''
insert @TRouting exec(@IRoutingID) SELECT T1.* into #TempFare FROM (
SELECT * FROM Fare WHERE ContractID =@ContractID
AND EXISTS(SELECT 1 FROM @TRouting R WHERE R.RoutingID=Fare.RoutingID )
)T1
INNER JOIN FareUsedSeason T2 ON T1.FareID = T2.FareID
INNER JOIN (SELECT * FROM SeasonDetail WHERE
ContractID =@ContractID AND @DepDate BETWEEN TravelFrom AND TravelTo
)T3 ON T2.SeasonID = T3.SeasonID -- Fare :: Tables[0]
Select * from #TempFare
-- WeekendCharge :: Tables[1]
Select * from #TempFare T1 inner join FareUsedWeekCharge T2 on T2.FareID = T1.FareID
inner join WeekCharge T3 ON T3.WeekChargeID = T2.WeekChargeID
INNER JOIN WeekChargeDefine T4 ON T4.WeekChargeID = T2.WeekChargeID -- BlackOutDate :: Tables[2]
SELECT * FROM #TempFare T1 INNER JOIN FareUsedBlackDay T2 ON T2.FareID = T1.FareID
INNER JOIN BlackOutDate T3 ON T3.BlackOutDateID = T2.BlackOutDateID
INNER JOIN BlackOutDateDefine T4 ON T4.BlackOutDateID = T2.BlackOutDateID -- Season :: Tables[3]
SELECT * FROM #TempFare T1 INNER JOIN FareUsedSeason T2 ON T2.FareID = T1.FareID
INNER JOIN SeasonDetail T3 ON T3.SeasonID = T2.SeasonID AND T3.ContractID = T1.ContractID -- Fare Basis :: Tables[4]
SELECT FareID, T2.*, T3.*, T4.RoundTrip as RoundTripDef, T4.OneWay as OneWayDef FROM #TempFare T1
INNER JOIN FareBasisGroup T2 ON T2.FareBasisGroupID = T1.FareBasisGroupID
INNER JOIN FareBasis T3 ON T3.FareBasisGroupID = T1.FareBasisGroupID
INNER JOIN FareBasisDef T4 ON T4.FareBasisID = T3.FareBasisID -- FlightRestriction :: Tables[5]
SELECT T1.*,T2.*,T3.*,T4.*, T5.SubRestrictionID, T5.SubRestrictionNo, T5.PAirlineID, T5.SAirlineID, T5.Class as SClass, T5.RoutingType as SRoutingType,
T5.DirectionRule AS SDirectionRule, T5.FlightIn AS SFlightIn, T5.FlightOut AS SFlightOut, T5.SpecificIn AS SSpecificIn,
T5.SpecificOut AS SSpecificOut,T6.SubRoutingID,T6.DepartureType as SDepartureType,T6.DestinationType as SDestinationType,T6.FeederType as SFeederType,
T6.InterlineType as SInterlineType,T6.Departure as SDeparture,T6.FeederGateway as SFeederGateway,
T6.InterlineGateway as SInterlineGateway,T6.Destination as SDestination
FROM #TempFare T1 INNER JOIN FareUsedRestriction T2 ON T2.FareID = T1.FareID
INNER JOIN (SELECT * FROM FlightRestriction where contractid=@ContractID) T3 ON T3.FlightRestrictionID = T2.FlightRestrictionID
LEFT JOIN MainToSubRes T4 ON T4.FlightRestrictionID = T2.FlightRestrictionID
LEFT JOIN SubRestriction T5 ON T5.SubRestrictionID = T4.SubRestrictionID
LEFT JOIN SubRouting T6 ON T6.SubRoutingID = T5.SubRoutingID ORDER BY T2.FlightRestrictionID主要是一些连接的优化,我在想能否把join之间的连接简化一些呢?
例如:
-- FlightRestriction :: Tables[5] LEFT JOIN MainToSubRes T4 ON T4.FlightRestrictionID = T2.FlightRestrictionID
LEFT JOIN SubRestriction T5 ON T5.SubRestrictionID = T4.SubRestrictionID
LEFT JOIN SubRouting T6 ON T6.SubRoutingID = T5.SubRoutingID ORDER BY T2.FlightRestrictionID请帮忙看看,特别是tim_spac哥们,真的谢谢你的帮忙?都不好意思麻烦你了?但是代码中的sql我写的太烂,仅仅是功能上的实现,希望能全面的优化 TRUNCATE TABLE #TempFare
DROP TABLE #TempFare
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetFareByContractID_DepDate_RoutingID]
@ContractID UNIQUEIDENTIFIER ,
@DepDate datetime,
@RoutingID VARCHAR(8000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;DECLARE @IRoutingID varchar(8000)
DECLARE @TRouting table( RoutingID UNIQUEIDENTIFIER)
select @IRoutingID='select '''+replace(@RoutingID,',',''' union all select ''')+''''
insert @TRouting exec(@IRoutingID) SELECT T1.* into #TempFare FROM (
SELECT * FROM Fare WHERE ContractID =@ContractID
AND EXISTS(SELECT 1 FROM @TRouting R WHERE R.RoutingID=Fare.RoutingID )
)T1
INNER JOIN FareUsedSeason T2 ON T1.FareID = T2.FareID
INNER JOIN (SELECT * FROM SeasonDetail WHERE
ContractID =@ContractID AND @DepDate BETWEEN TravelFrom AND TravelTo
)T3 ON T2.SeasonID = T3.SeasonID -- Fare :: Tables[0]
Select * from #TempFare
-- WeekendCharge :: Tables[1]
Select * from #TempFare T1 inner join FareUsedWeekCharge T2 on T2.FareID = T1.FareID
inner join WeekCharge T3 ON T3.WeekChargeID = T2.WeekChargeID
INNER JOIN WeekChargeDefine T4 ON T4.WeekChargeID = T2.WeekChargeID -- BlackOutDate :: Tables[2]
SELECT * FROM #TempFare T1 INNER JOIN FareUsedBlackDay T2 ON T2.FareID = T1.FareID
INNER JOIN BlackOutDate T3 ON T3.BlackOutDateID = T2.BlackOutDateID
INNER JOIN BlackOutDateDefine T4 ON T4.BlackOutDateID = T2.BlackOutDateID -- Season :: Tables[3]
SELECT * FROM #TempFare T1 INNER JOIN FareUsedSeason T2 ON T2.FareID = T1.FareID
INNER JOIN SeasonDetail T3 ON T3.SeasonID = T2.SeasonID AND T3.ContractID = T1.ContractID -- Fare Basis :: Tables[4]
SELECT FareID, T2.*, T3.*, T4.RoundTrip as RoundTripDef, T4.OneWay as OneWayDef FROM #TempFare T1
INNER JOIN FareBasisGroup T2 ON T2.FareBasisGroupID = T1.FareBasisGroupID
INNER JOIN FareBasis T3 ON T3.FareBasisGroupID = T1.FareBasisGroupID
INNER JOIN FareBasisDef T4 ON T4.FareBasisID = T3.FareBasisID -- FlightRestriction :: Tables[5]
SELECT T1.*,T2.*,T3.*,T4.*, T5.SubRestrictionID, T5.SubRestrictionNo, T5.PAirlineID, T5.SAirlineID, T5.Class as SClass, T5.RoutingType as SRoutingType,
T5.DirectionRule AS SDirectionRule, T5.FlightIn AS SFlightIn, T5.FlightOut AS SFlightOut, T5.SpecificIn AS SSpecificIn,
T5.SpecificOut AS SSpecificOut,T6.SubRoutingID,T6.DepartureType as SDepartureType,T6.DestinationType as SDestinationType,T6.FeederType as SFeederType,
T6.InterlineType as SInterlineType,T6.Departure as SDeparture,T6.FeederGateway as SFeederGateway,
T6.InterlineGateway as SInterlineGateway,T6.Destination as SDestination
FROM #TempFare T1 INNER JOIN FareUsedRestriction T2 ON T2.FareID = T1.FareID
INNER JOIN (SELECT * FROM FlightRestriction where contractid=@ContractID) T3 ON T3.FlightRestrictionID = T2.FlightRestrictionID
LEFT JOIN MainToSubRes T4 ON T4.FlightRestrictionID = T2.FlightRestrictionID
LEFT JOIN SubRestriction T5 ON T5.SubRestrictionID = T4.SubRestrictionID
LEFT JOIN SubRouting T6 ON T6.SubRoutingID = T5.SubRoutingID ORDER BY T2.FlightRestrictionID TRUNCATE TABLE #TempFare
DROP TABLE #TempFareEND