我可以缩短这个SQL查询吗？

I have write a query to get the total number assignments which had no activity from last 5 months including those assignments which were created five months ago and never had any visit on it.

我已经编写了一个查询来获取过去5个月没有活动的总数分配,包括那些在五个月前创建并且从未对其进行任何访问的分配。

Can i shorten this query any further so i don't have to use the outer query.

我可以进一步缩短此查询,因此我不必使用外部查询。

  SELECT @NumNoActivity = COUNT(QnoActivity.AssignmentID) FROM

  (

    SELECT a.AssignmentID
    FROM Assignments a
    LEFT JOIN VISITS v ON v.AssignmentID = a.AssignmentID
    WHERE a.CurrentStatus = 1  
    AND a.StaffID = @StaffID
    GROUP BY a.AssignmentID,  a.CreatedDate
    HAVING DATEDIFF(MONTH, ISNULL(MAX(v.VisitDate),a.CreatedDate ), GETDATE())  > =5

  ) QnoActivity

2 个解决方案

#1

You could use NOT EXISTS to remove the join:

您可以使用NOT EXISTS删除连接:

SELECT @NumNoActivity = COUNT(a.AssignmentID)
FROM Assignments a
WHERE a.CurrentStatus = 1  
AND a.StaffID = @StaffID
AND NOT EXISTS
        (SELECT * FROM VISITS v 
         WHERE v.AssignmentID = a.AssignmentID
         AND v.VisitDate>DATEADD(month,-5,GETDATE()))

UPDATE

Based on Gordon Linoff's comment, the query was missing the Assignments.CreatedDate condition. Here's an updated version:

根据Gordon Linoff的评论,该查询缺少Assignments.CreatedDate条件。这是一个更新版本:

SELECT @NumNoActivity = COUNT(a.AssignmentID)
FROM Assignments a
WHERE a.CurrentStatus = 1  
AND a.StaffID = @StaffID
AND (SELECT ISNULL(MAX(VisitDate), a.CreatedDate) FROM VISITS v 
         WHERE v.AssignmentID = a.AssignmentID) <= DATEADD(month,-5,GETDATE())

Here is SQL Fiddle to show how it works. Assignment results are:

这是SQL Fiddle,展示它是如何工作的。作业结果如下:

1 - not included as there's a recent visit

1 - 不包括在内,因为最近有一次访问

2 - included as there's no visit and create date is old

2 - 包括因为没有访问和创建日期是旧的

3 - included as there's only a recent visit

3 - 包含因为最近只有一次访问

4 - not included as there's no visit but create date is recent

4 - 不包括在内,因为没有访问但创建日期是最近的

5 - not included as there's a recent visit

5 - 不包括在内,因为最近有一次访问

#2

There is an alternative way to write this query. You can look at it as the difference etween the total number of "assignments" and the total number of "assignments" that have had activity in the past 4/5 months.

还有另一种编写此查询的方法。您可以将其视为“分配”总数与过去4/5个月内有活动的“分配”总数之间的差异。

The following takes this approach. It excludes new "assignments" in the where clause and the counts the different in the select clause:

以下采用这种方法。它排除了where子句中的新“赋值”,并对select子句中的不同进行了计数:

SELECT (count(distinct a.AssignmentID) -
        count(distinct case when datediff(MONTH, v.VisitDate, GETDATE()) < 5 or
                                 v.VisitDate is null
                            then a.AssignmentId end)
       )
FROM Assignments a LEFT JOIN
     VISITS v
     ON v.AssignmentID = a.AssignmentID
WHERE a.CurrentStatus = 1 AND
      a.StaffID = @StaffID and
      a.CreatedDate <= DATEADD(month, -5, GETDATE());

#1