废话不多说，直接上sql
SELECT top 1 B.CREW_ID, E.CREW_NAME,C.OFFBLK,C.ONBLK,dbo.PEK_OPS_Date(A.STD) as STD FROM dbo.FLIGHTS A LEFT OUTER JOIN (SELECT DISTINCT TASK_ID, CREW_ID FROM dbo.CREW_TASK ) B ON A.COCKPIT_CREW_GROUP = B.TASK_ID LEFT OUTER JOIN dbo.FLIGHTS_CREW_REPORT C ON A.FLTID = C.FLTID LEFT JOIN CREW_INFO E ON B.CREW_ID = E.CREW_ID LEFT JOIN FLIGHTS_STC F ON A.STC = F.IATA_CODE WHERE (A.COCKPIT_CREW_GROUP > 0) AND (A.STATUS=‘ATA‘) AND F.RATIO = 1 AND NOT (A.AC LIKE ‘%--%‘) AND B.CREW_ID = ISNULL(60, B.CREW_ID) AND C.OFFBLK is not null AND C.ONBLK is not null AND C.CREW_LEFT is not null AND C.CREW_RIGHT is not null ORDER BY A.STD desc
执行计划如下：
执行时间：
16秒、
分析：
嵌套循环之前，进行了排序操作，驱动表CREW_TASK 单表数据量300232。去重排序之后29w+。FLIGHTS 表单表44w+，这里不走hash 的原因是因为top1导致。故去掉 TOP 1 限制语句
SELECT B.CREW_ID, E.CREW_NAME,C.OFFBLK,C.ONBLK,dbo.PEK_OPS_Date(A.STD) as STD FROM dbo.FLIGHTS A LEFT OUTER JOIN (SELECT DISTINCT TASK_ID, CREW_ID FROM dbo.CREW_TASK ) B ON A.COCKPIT_CREW_GROUP = B.TASK_ID LEFT OUTER JOIN dbo.FLIGHTS_CREW_REPORT C ON A.FLTID = C.FLTID LEFT JOIN CREW_INFO E ON B.CREW_ID = E.CREW_ID LEFT JOIN FLIGHTS_STC F ON A.STC = F.IATA_CODE WHERE (A.COCKPIT_CREW_GROUP > 0) AND (A.STATUS=‘ATA‘) AND F.RATIO = 1 AND NOT (A.AC LIKE ‘%--%‘) AND B.CREW_ID = ISNULL(60, B.CREW_ID) AND C.OFFBLK is not null AND C.ONBLK is not null AND C.CREW_LEFT is not null AND C.CREW_RIGHT is not null ORDER BY A.STD desc
执行计划：
执行时间：
分析：很明显hash走了hash 匹配，可见在双大表情况下，hash 匹配返回5000多行比嵌套循环返回一条要快得多。
方向明确，那么怎么取上述第一条数据呢？这个时候用到 TOP percent，也就是返回结果集的一个比例值，而不是 top 1 第一条数据：
sql如下：
SELECT top 100 percent B.CREW_ID, E.CREW_NAME,C.OFFBLK,C.ONBLK,dbo.PEK_OPS_Date(A.STD) as STD FROM dbo.FLIGHTS A LEFT OUTER JOIN (SELECT DISTINCT TASK_ID, CREW_ID FROM dbo.CREW_TASK ) B ON A.COCKPIT_CREW_GROUP = B.TASK_ID LEFT OUTER JOIN dbo.FLIGHTS_CREW_REPORT C ON A.FLTID = C.FLTID LEFT JOIN CREW_INFO E ON B.CREW_ID = E.CREW_ID LEFT JOIN FLIGHTS_STC F ON A.STC = F.IATA_CODE WHERE (A.COCKPIT_CREW_GROUP > 0) AND (A.STATUS=‘ATA‘) AND F.RATIO = 1 AND NOT (A.AC LIKE ‘%--%‘) AND B.CREW_ID = ISNULL(60, B.CREW_ID) AND C.OFFBLK is not null AND C.ONBLK is not null AND C.CREW_LEFT is not null AND C.CREW_RIGHT is not null ORDER BY A.STD desc
执行时间 
，返回100%的结果集
怎么返回第一条？很简单，我返回1%的结果集如下：
SELECT top 1 percent B.CREW_ID, E.CREW_NAME,C.OFFBLK,C.ONBLK,dbo.PEK_OPS_Date(A.STD) as STD FROM dbo.FLIGHTS A LEFT OUTER JOIN (SELECT DISTINCT TASK_ID, CREW_ID FROM dbo.CREW_TASK ) B ON A.COCKPIT_CREW_GROUP = B.TASK_ID LEFT OUTER JOIN dbo.FLIGHTS_CREW_REPORT C ON A.FLTID = C.FLTID LEFT JOIN CREW_INFO E ON B.CREW_ID = E.CREW_ID LEFT JOIN FLIGHTS_STC F ON A.STC = F.IATA_CODE WHERE (A.COCKPIT_CREW_GROUP > 0) AND (A.STATUS=‘ATA‘) AND F.RATIO = 1 AND NOT (A.AC LIKE ‘%--%‘) AND B.CREW_ID = ISNULL(60, B.CREW_ID) AND C.OFFBLK is not null AND C.ONBLK is not null AND C.CREW_LEFT is not null AND C.CREW_RIGHT is not null ORDER BY A.STD desc
执行时间：
，很明显返回的是1%的结果集
返回0.01的结果集：
SELECT top 0.01 percent B.CREW_ID, E.CREW_NAME,C.OFFBLK,C.ONBLK,dbo.PEK_OPS_Date(A.STD) as STD FROM dbo.FLIGHTS A LEFT OUTER JOIN (SELECT DISTINCT TASK_ID, CREW_ID FROM dbo.CREW_TASK ) B ON A.COCKPIT_CREW_GROUP = B.TASK_ID LEFT OUTER JOIN dbo.FLIGHTS_CREW_REPORT C ON A.FLTID = C.FLTID LEFT JOIN CREW_INFO E ON B.CREW_ID = E.CREW_ID LEFT JOIN FLIGHTS_STC F ON A.STC = F.IATA_CODE WHERE (A.COCKPIT_CREW_GROUP > 0) AND (A.STATUS=‘ATA‘) AND F.RATIO = 1 AND NOT (A.AC LIKE ‘%--%‘) AND B.CREW_ID = ISNULL(60, B.CREW_ID) AND C.OFFBLK is not null AND C.ONBLK is not null AND C.CREW_LEFT is not null AND C.CREW_RIGHT is not null ORDER BY A.STD desc
执行时间：
已经达到要求。
注意：这里返回的是结果集的 百分比，虽然现在满足取第一条的要求，但是是随数据量增大，可能会多条， 故，外面包一层，取TOP 1 这样能保证只取一条。
select top 1 AA.* from ( SELECT top 1 percent B.CREW_ID, E.CREW_NAME,C.OFFBLK,C.ONBLK,dbo.PEK_OPS_Date(A.STD) as STD FROM dbo.FLIGHTS A LEFT OUTER JOIN (SELECT DISTINCT TASK_ID, CREW_ID FROM dbo.CREW_TASK ) B ON A.COCKPIT_CREW_GROUP = B.TASK_ID LEFT OUTER JOIN dbo.FLIGHTS_CREW_REPORT C ON A.FLTID = C.FLTID LEFT JOIN CREW_INFO E ON B.CREW_ID = E.CREW_ID LEFT JOIN FLIGHTS_STC F ON A.STC = F.IATA_CODE WHERE (A.COCKPIT_CREW_GROUP > 0) AND (A.STATUS=‘ATA‘) AND F.RATIO = 1 AND NOT (A.AC LIKE ‘%--%‘) AND B.CREW_ID = ISNULL(60, B.CREW_ID) AND C.OFFBLK is not null AND C.ONBLK is not null AND C.CREW_LEFT is not null AND C.CREW_RIGHT is not null ORDER BY A.STD desc )AA
执行时间：
收工！
原文：https://www.cnblogs.com/monkeybron/p/10521567.html