复杂的SQL Server查询 [英] Complicated SQL Server query

问题描述

我正在尝试编写一个SQL（服务器）查询，该查询将返回当日的所有事件，并且对于重复发生的所有事件 = 1，我想要它会在举办当天以及活动之后的52周内返回该活动。

I am trying to write an SQL (Server) query which will return all events on a current day, and for all events where the column recurring= 1, I want it to return this event on the day it is being held and for the subsequent 52 weeks following the event.

我的表格结构如下：

Event
{
    event_id (PK)
    title,
    description,
    event_start DATETIME,
    event_end DATETIME,
    group_id,
    recurring
}

Users
{
    UserID (PK)
    Username
}

Groups
{
    GroupID (PK)
    GroupName
}

Membership
{
    UserID (FK)
    GroupID (FK)
}

到目前为止，我的代码如下：

The code I have thus far is as follows :

     var db = Database.Open("mPlan");
    string username = HttpContext.Current.Request.Cookies.Get("mpUsername").Value;
    var listOfGroups = db.Query("SELECT GroupID FROM Membership WHERE UserID = (SELECT UserID from Users WHERE Username = @0 )",  username);
    foreach(var groupID in listOfGroups)
        {
            int newGroupID = groupID.GroupID;
            var result = db.Query(
                @"SELECT e.event_id, e.title, e.description, e.event_start, e.event_end, e.group_id, e.recurring
                FROM   event e
                JOIN   Membership m ON m.GroupID = e.group_id
                WHERE  e.recurring = 0
                AND    m.GroupID = @0
                AND    e.event_start >= @1
                AND    e.event_end <= @2
                UNION ALL
                SELECT e.event_id, e.title, e.description, DATEADD(week, w.weeks, e.event_start), DATEADD(week, w.weeks, e.event_end), e.group_id, e.recurring
                FROM   event e
            JOIN   Membership m ON m.GroupID = e.group_id
            CROSS JOIN 
                ( SELECT  row_number() OVER (ORDER BY Object_ID) AS weeks
                FROM SYS.OBJECTS
                ) AS w
                WHERE  e.recurring = 1
                AND    m.GroupID = @3
                AND DATEADD(WEEK, w.Weeks, e.event_start) >= @4 
                AND DATEADD(WEEK, w.Weeks, e.event_end) <= @5", newGroupID, start, end, newGroupID, start, end
            );

这导致当一个查询存储在数据库中的事件的日期时，该事件和52返回数周的事件。当一个事件在事件发生后的一周内查询该事件时，什么也不会返回。

This results in when one queries for the date of the event stored in the database, this event and 52 weeks of events are returned. When one queries for the event the week after this one, nothing is returned.

推荐答案

最简单的解决方案是更改以下内容2行

The simplest solution would be to alter the following 2 lines

AND    e.event_start >= @4
AND    e.event_end <= @5"

到

AND    DATEADD(WEEK, w.Weeks, e.event_start) >= @4
AND    DATEADD(WEEK, w.Weeks, e.event_end) <= @5"

但是，我建议将所有这些SQL放入存储过程中，SQL-Server将缓存执行计划，结果（略）有更好的表现。

However, I'd advise putting all this SQL into a stored procedure, SQL-Server will cache the execution plans and it will result in (slightly) better performance.

CREATE PROCEDURE dbo.GetEvents @UserName VARCHAR(50), @StartDate DATETIME, @EndDate DATETIME
AS
BEGIN
-- DEFINE A CTE TO GET ALL GROUPS ASSOCIATED WITH THE CURRENT USER
;WITH Groups AS 
(   SELECT  GroupID 
    FROM    Membership  m
            INNER JOIN Users u
                ON m.UserID = u.UserID
    WHERE   Username = @UserName
    GROUP BY GroupID
),
-- DEFINE A CTE TO GET ALL EVENTS FOR THE GROUPS DEFINED ABOVE
AllEvents AS
(   SELECT  e.*
    FROM    event e
            INNER JOIN Groups m 
                ON m.GroupID = e.group_id
    UNION ALL
    SELECT  e.event_id, e.title, e.description, DATEADD(WEEK, w.weeks, e.event_start), DATEADD(WEEK, w.weeks, e.event_end), e.group_id, e.recurring
    FROM    event e
            INNER JOIN Groups m 
                ON m.GroupID = e.group_id
            CROSS JOIN 
            (   SELECT  ROW_NUMBER() OVER (ORDER BY Object_ID) AS weeks
                FROM    SYS.OBJECTS
            ) AS w
    WHERE  e.recurring = 1
)   
-- GET ALL EVENTS WHERE THE EVENTS FALL IN THE PERIOD DEFINED
SELECT  *
FROM    AllEvents
WHERE   Event_Start >= @StartDate
AND     Event_End <= @EndDate

END

然后您可以使用

var result = db.Query("EXEC dbo.GetEvents @0, @1, @2", username, start, end);

这样就无需遍历后面代码中的组。如果确实需要这样做，则可以修改存储过程，以@GroupID作为参数，并根据需要更改select语句/ where子句。

This elimates the need to iterate over groups in your code behind. If this is actually a requirement then you could modify the stored procedure to take @GroupID as a parameter, and change the select statements/where clauses as necessary.

我假设公用表表达式的知识。在我看来，它们不需要使查询正常工作，它们只是使事情变得更清晰易读。如果需要，我可以不用它们来重写它。

I have assumed knowledge of Common Table Expressions. They are not required to make the query work, they just make things slightly more legible in my opinion. I can rewrite this without them if required.

这篇关于复杂的SQL Server查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！