从过去的所有学生中抽取学生

Question

我试图从他们过去所有入学的学生中抽取出来。

• 有些学生可以有一个以上的入学日期，其中一个结束日期是在未来，而另一个是在过去。

在上面的样本数据中，我只想在我的结果中得到一个记录'BBB‘。

我通过比较no来使用CTE来实现这个目标。总记录与无。过去的总记录。

WITH T1 AS
(SELECT
    RefNo
    , Total = COUNT(RecordID)
    , TotalPastRecords = SUM(IIF(EndDate < GETDATE(), 1, 0))
FROM
    Enrolments
GROUP BY
    RefNo)
SELECT
  *
FROM
  T1
WHERE
  Total = TotalPastRecords

你能帮我检查一下上面的代码吗?如果有改进的方法，请告诉我。

我们的注册表包含大约200,000行，这个查询将连接到另一个4-5个表，以获得最终结果。

Answer 1

既然您说过要加入到其他一些表，那么似乎只需要满足您的标准的RefNo。您可以同时使用HAVING和删除CTE。

DECLARE @Enrollments TABLE(RecordID int, RefNo char(3), EndDate datetime)
INSERT INTO @Enrollments VALUES
(1,'AAA','1/1/2018'),
(2,'AAA','1/2/2018'),
(3,'BBB','1/1/2016'),
(4,'BBB','1/5/2016'),
(5,'CCC','1/5/2016'),
(6,'CCC','1/1/2018')


--Get the RefNo's that don't have a future date
SELECT
    RefNo
FROM
    @Enrollments
GROUP BY 
    RefNo
HAVING MAX(EndDate) < CAST(GETDATE() AS DATE)

--Get all rows for RefNo's that don't have a future date
SELECT
    RecordID
    ,RefNo
    ,EndDate
FROM
    @Enrollments
WHERE 
    RefNo IN(
             SELECT RefNo 
             FROM @Enrollments 
             GROUP BY RefNo 
             HAVING MAX(EndDate) < CAST(GETDATE() AS DATE)
             )

如果您只是需要“刷新”来限制另一个表的结果。然后，您可以在IN中使用第一个查询，类似于第二个查询是如何编写的。

你可以在这里看到执行计划将您所使用的CTE方法与第二个查询中提供的sub-query进行比较。就我而言，该计划的具体内容如下：

Query Cost (Relative to the Batch)
INSERT INTO @Enrollments....24%
SUBQUERY Example using IN...16%
INSERT INTO @Enrollments....24%
Query with CTE Example......36%

所以CTE要慢一些。当然，在你的生活环境中会有很多索引，加入你正在做的事情等等，但至少这是你可以考虑的另一种选择。

最后，根据索引和联接的不同，在CTE、Table变量或Table中引入临时结果集有时会更快。在我的大多数情况下，临时表是最快的，但对于您的环境来说可能有所不同。您已经关闭了CTE方法，尽管我的做法略有不同。这里有一些例子。

--stage the RefNo in a temp table
IF OBJECT_ID('tempdb..#RefNo') IS NOT NULL DROP TABLE #RefNo

SELECT
    RefNo
INTO #RefNo
FROM
    @Enrollments
GROUP BY 
    RefNo
HAVING MAX(EndDate) < CAST(GETDATE() AS DATE)

SELECT
    ...
FROM
    YourTable t
    INNER JOIN
    #RefNo n on t.RefNo = n.RefNo



--use a CTE to limit the RefNo. Understand the optimizer may not logically execute this how it is written
WITH cte AS(
    SELECT
        RefNo
    FROM
        @Enrollments
    GROUP BY 
        RefNo
    HAVING MAX(EndDate) < CAST(GETDATE() AS DATE))

SELECT
    ...
FROM
    YourTable t
    INNER JOIN
    cte c on t.RefNo = c.RefNo


--using a table variable, which I wouldn't do in most cases
DECLARE @RefNo TABLE (RefNo CHAR(3))

INSERT INTO @RefNo
    SELECT
        RefNo
    FROM
        @Enrollments
    GROUP BY 
        RefNo
    HAVING MAX(EndDate) < CAST(GETDATE() AS DATE)

SELECT
    ...
FROM
    YourTable t
    INNER JOIN
    @RefNo rn on t.RefNo = rn.RefNo