MS-SQL Server
SQL优化！！大家帮忙看看那些地方写法需要优化啊！
墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年，写一次发全网
我也要 关注 + 加入社区
ALTER TEST [dbo].[TEST]
@Dep datetime,
@Rtn datetime,
@DepAirPort NVARCHAR(10),
@RtnAirPort NVARCHAR(10),
@Airways NVARCHAR(100),
@TripType NVARCHAR(10),
@FareType NVARCHAR(10)
AS
SET LANGUAGE US_ENGLISH
DECLARE @IsWeekday NVARCHAR(10)
SET @IsWeekday = SUBSTRING(DATENAME(weekday,@dep),1,3)
DECLARE @RtnIsWeekday NVARCHAR(10)
SET @RtnIsWeekday = SUBSTRING(DATENAME(weekday,@Rtn),1,3)
DECLARE @NewDepAirport VARCHAR(30)
DECLARE @NewDesAirport VARCHAR(30)
DECLARE @IDepAirport NVARCHAR(200)
DECLARE @IDesAirport NVARCHAR(200)
/*
Get the same city code
*/
SELECT @NewDepAirport = Mc.ocitycode + ',' + Mc.samecitycode
FROM MST_ComCity MC WHERE Ocitycode = @DepAirPort
SELECT @NewDesAirport = Mc.ocitycode + ',' + Mc.samecitycode
FROM MST_ComCity MC WHERE Ocitycode = @RtnAirPort
/*
check if there are the same city code in the MST_ComCity TABLE.
if not, THEN set the input values
*/
IF @NewDepAirport IS NULL
SET @NewDepAirport=@DepAirport
IF @NewDesAirport IS NULL
SET @NewDesAirport=@RtnAirPort
/*
Declare a temp TABLE
*/
DECLARE @DepCityCode TABLE( cityCode NVARCHAR(10))
DECLARE @RtnCityCode TABLE( cityCode NVARCHAR(10))
/*
Dep Airport
*/
SELECT @IDepAirport='SELECT '''+replace(@NewDepAirport,',',''' UNION ALL SELECT ''')+''''
INSERT @DepCityCode EXEC(@IDepAirport)
/*
Rtn airport
*/
SELECT @IDesAirport='SELECT '''+replace(@NewDesAirport,',',''' UNION ALL SELECT ''')+''''
INSERT @RtnCityCode EXEC(@IDesAirport)
/*
Airways check
*/
DECLARE @AirCode TABLE( Code NVARCHAR(10) )
DECLARE @allAirway NVARCHAR(2000)
IF @airways <> 'ALL'
BEGIN
SELECT @allAirway='SELECT '''+replace(@airways,',',''' UNION ALL SELECT ''')+''''
INSERT @AirCode EXEC(@allAirway)
END
ELSE
INSERT @AirCode (Code) SELECT airlinecode FROM MST_Airlines
/*
Get useFul contract
*/
SELECT DISTINCT ContractInfo.contractid,FareType,OnlineOnly,TicketBy INTO #ContractInfo
FROM ContractInfo
WHERE
(
(
CASE
WHEN @FareType = 'NET' THEN 'NET'
WHEN @FareType = 'COMM' THEN 'COMM'
END = FareType
)
OR
(
CASE
WHEN @FareType = 'NETCOMM'
THEN 'ATPCO'
END <> FareType
)
)
AND ReleaseFlag = 1
AND TicketBy >= convert(nvarchar(10),getdate(),101)--DateAdd(HOUR,-23,GETDATE())
AND EXISTS
(
SELECT 1 FROM GeneralRule
WHERE
SellFROM <= CONVERT(nvarchar(10),GETDATE(),101)
AND ( DATEDIFF(dd,GETDATE(),@dep) >= AdvancePurchase )
AND ContractID = ContractInfo.ContractID
)
AND EXISTS
(
SELECT 1 FROM MST_Airlines
WHERE AirlineID = ContractInfo.AirlineID
AND EXISTS (
SELECT 1 FROM @AirCode airCode
WHERE airCode.Code = MST_Airlines.AirLineCode
)
)
/*
feeder
*/
SELECT R.* INTO #feeder FROM
(SELECT * FROM routing WHERE routingtype='FEEDER')R
INNER JOIN #ContractInfo C ON C.contractid=R.contractid
INNER JOIN @DepCityCode Dep ON PATINDEX('%'+ Dep.cityCode + '%',R.Departure) >0
/*
Interline
*/
SELECT R.* INTO #interline FROM
(SELECT * FROM routing WHERE routingtype='INTERLINE')R
INNER JOIN #ContractInfo C ON C.contractid=R.contractid
INNER JOIN @RtnCityCode Rtn ON PATINDEX('%'+ Rtn.cityCode +'%',R.Destination) >0
/*
Online
*/
SELECT R.*,C.FareType,C.OnlineOnly,C.TicketBy INTO #ONLINE FROM routing R
INNER JOIN #ContractInfo C ON C.contractid=R.contractid
WHERE R.routingtype='ONLINE'
/*
Set the all contractID 2 Temp TABLE
*/
SELECT DISTINCT contractid INTO #contractid FROM #ONLINE
/*
Online Fare
*/
SELECT O.* INTO #FARE_O FROM FARE O
INNER JOIN #contractid C ON C.contractid=O.contractid
WHERE O.AddOnFlag='0'
AND BlockFare='0'
AND (
CASE @TripType
WHEN '1' THEN RTAdult
WHEN '0' THEN OWAdult
END >0
)
/*
Feeder Fare
*/
SELECT F.* INTO #FARE_F FROM FARE F
INNER JOIN #contractid C ON C.contractid=F.contractid
WHERE F.AddOnFlag='1'
AND F.GateWayType='1'
AND BlockFare='0'
AND (
CASE @TripType
WHEN '1' THEN RTAdult
WHEN '0' THEN OWAdult
END >0
)
/*
InterLine Fare
*/
SELECT I.* INTO #FARE_I FROM FARE I
INNER JOIN #contractid C ON C.contractid=I.contractid
WHERE I.AddOnFlag='1'
AND I.GateWayType='0'
AND BlockFare='0'
AND (
CASE @TripType
WHEN '1' THEN RTAdult
WHEN '0' THEN OWAdult
END >0
)
/*
online(2 + 3)
*/
SELECT * INTO #online23 FROM #ONLINE R
WHERE R.departure IS NULL
and R.feedergateway is not null and R.InterlineGateway is not null
and R.destination is NULL
/*
Onlne(1 + 2 + 3)
*/
SELECT * INTO #online123 FROM
(
SELECT * FROM #ONLINE O
WHERE O.departure IS NOT NULL
and (O.feedergateway is not null and O.InterlineGateway is not null)
and O.destination is NULL
)R
INNER JOIN @DepCityCode Dep ON PATINDEX('%'+ Dep.cityCode + '%',R.Departure) >0
/*
Online(2 + 3 + 4)
*/
SELECT * INTO #online234 FROM
(
SELECT * FROM #ONLINE O
WHERE O.departure IS NULL
AND O.feedergateway is not null
AND O.InterlineGateway is not null
AND O.destination is NOT NULL
)R
INNER JOIN @RtnCityCode Rtn ON PATINDEX('%'+ Rtn.cityCode +'%',R.Destination) >0
/*
TravelFROM and TravelTo check TABLE
*/
SELECT SeasonName,TravelFROM,TravelTo,contractid,TicketingBy INTO #SD
FROM SeasonDetail S
INNER JOIN MST_Season MS ON s.SeasonID=MS.SeasonID
WHERE @Dep BETWEEN S.TravelFROM AND S.TravelTo
AND CONVERT(nvarchar(10),GETDATE(),101) <= S.TicketingBy
/* The 1st
12 + 23 + 34
*/
SELECT
F.departure,
F.feedergateway AS feedergateway1,
O23.feedergateway AS feedergateway2,
O23.InterlineGateway AS InterlineGateway1,
T1.InterlineGateway AS InterlineGateway2,
T1.Destination,
O23.FareType,
O23.OnlineOnly,
O23.TicketBy,
O1.FareID,
O1.FareNo, F1.FareNO AS F1No, I1.FareNO AS I1No,
O1.contractid,
O1.FareBasisGroupID,
O1.MinStayType ,
O1.MinStay,
O1.MaxStayType,
O1.MaxStay,
dbo.[GetCombineAiline](O1.airlineid,F1.airlineid,I1.airlineid) Airline,
O1.BookingClass + '/' + F1.BookingClass + '/' + I1.BookingClass AS BookingClass,
(O1.OWChild + F1.OWChild + I1.OWChild) OWChild,
(O1.RTChild + F1.RTChild + I1.RTChild) RTChild,
(O1.RTAdult + F1.RTAdult + I1.RTAdult) RTAdult,
(O1.OWAdult + F1.OWAdult + I1.OWAdult) OWAdult,
O1.SeasonInfor
,'122334' AS FN INTO #TempFARE1
FROM #feeder F
INNER JOIN AddOnRouting AOR ON F.routingid=AOR.routingid
INNER JOIN #online23 O23 ON O23.routingid =AOR.mainroutingid
INNER JOIN
(
SELECT I.*,AOR.mainroutingid FROM #interline I
INNER JOIN AddOnRouting AOR ON I.routingid=AOR.routingid
)T1
ON T1.mainroutingid = O23.routingid
INNER JOIN #FARE_F F1 ON F1.routingID=F.Routingid
INNER JOIN #FARE_I I1 ON I1.routingID =T1.Routingid
INNER JOIN #FARE_O O1 ON O23.routingID= O1.RoutingID
-- 2
-- 123 + 34
SELECT
O123.departure,
O123.feedergateway AS feedergateway1,
O123.feedergateway AS feedergateway2,
O123.InterlineGateway AS InterlineGateway1,
I34.InterlineGateway As InterlineGateway2,
I34.Destination ,
O123.FareType,
O123.OnlineOnly,
O123.TicketBy,
O1.FareID,
O1.FareNo,'' AS F1No,I1.FareNo AS I1No,
O1.contractid,
O1.FareBasisGroupID,
O1.MinStayType ,
O1.MinStay,
O1.MaxStayType,
O1.MaxStay,
dbo.[GetCombineAiline](O1.airlineid,null,I1.airlineid) Airline,
O1.BookingClass + '/-' + '/' + I1.BookingClass AS BookingClass,
(O1.OWChild + I1.OWChild) OWChild,
(O1.RTChild + I1.RTChild) RTChild,
(O1.RTAdult + I1.RTAdult) RTAdult,
(O1.OWAdult + I1.OWAdult) OWAdult,
O1.SeasonInfor
,'12334' AS FN INTO #TempFARE2
FROM #online123 O123
INNER JOIN (
SELECT mainroutingid,I.Destination,I.InterlineGateway,I.Routingid FROM #interline I
INNER JOIN AddOnRouting AOR ON I.RoutingID=AOR.routingid
)I34 ON O123.routingID=I34.mainroutingid
INNER JOIN #FARE_I I1 ON I1.routingID =I34.Routingid
INNER JOIN #FARE_O O1 ON O123.routingID= O1.RoutingID
-- 3
-- 12 + 234
SELECT
F.departure,
F.feedergateway AS feedergateway1,
O234.feedergateway AS feedergateway2,
O234.InterlineGateway AS InterlineGateway1,
O234.InterlineGateway AS InterlineGatewa2,
O234.Destination,
O234.FareType,
O234.OnlineOnly,
O234.TicketBy,
O1.FareID,
O1.FareNo,'' AS F1No,F1.FareNo AS I1No,
O1.contractid,
O1.FareBasisGroupID,
O1.MinStayType ,
O1.MinStay,
O1.MaxStayType,
O1.MaxStay,
dbo.[GetCombineAiline](O1.airlineid,F1.airlineid,NULL) Airline,
O1.BookingClass + '/' + F1.BookingClass + '/-' AS BookingClass,
(O1.OWChild + F1.OWChild) OWChild,
(O1.RTChild + F1.RTChild) RTChild,
(O1.RTAdult + F1.RTAdult) RTAdult,
(O1.OWAdult + F1.OWAdult) OWAdult,
O1.SeasonInfor
,'12234' AS FN INTO #TempFARE3
FROM #feeder F
INNER JOIN AddOnRouting AOR ON F.routingid=AOR.routingid
INNER JOIN
#online234 O234 ON O234.routingid =AOR.mainroutingid
INNER JOIN #FARE_O O1 ON O234.routingID= O1.RoutingID
INNER JOIN #FARE_F F1 ON F1.routingID =F.Routingid
...全文
相关推荐
绝对惊世骇俗的SQL写法
“Mysql神髓”这一标签进一步强调了这些SQL写法的精髓所在，意味着它们可能包含了MySQL数据库系统中的核心概念和技术，如优化查询性能、高效的数据处理以及复杂的联接操作等。
从两种SQL表连接写法来了解过去
- **兼容性**：如果需要在不同数据库系统间迁移，可能需要使用更古老的写法以确保兼容性。- **性能**：虽然性能差异微乎其微，但在特定情况下，如数据库优化器的处理方式，可能会导致性能上的微妙差别。
SQL优化 SQL优化软件 SQL优化工具
因此，SQL优化对于保持系统的高效运行至关重要。SQL优化工具通常提供以下功能：1.
sql分页查询几种写法
SQL分页查询是数据库操作中常见的一种需求，特别是在大数据量的场景下，为了提高用户体验，通常需要对查询结果进行分页展示。以下是一些常见的SQL分页查询的写法：1.
数据分布决定SQL写法
"数据分布决定SQL写法是一个关键的概念，主要体现在如何根据数据库中的数据分布情况来优化SQL查询。在2016年的上海Oracle大会上，陈宏义老师分享了一个案例，通过将一个复杂的Merge SQL
