复杂的SQL Server查询

本文关键字：查询 Server SQL 复杂 | 更新日期: 2023-09-27 18:27:38

我正在尝试编写一个SQL（Server）查询，该查询将返回当天的所有事件，对于列recurring=1的所有事件来说，我希望它在举行当天以及事件发生后的52周内返回此事件。

我的表格结构如下：

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)
}

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

     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周的事件。当查询此事件后的一周的事件时，将不返回任何内容。

复杂的SQL Server查询

最简单的解决方案是更改以下两行

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将缓存执行计划，这将导致（稍微）更好的性能。

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子句。

我假定知道常用表表达式。他们不需要让查询工作，在我看来，他们只是让事情稍微清晰一些。如果需要，我可以在没有它们的情况下重写。

我会根据一些琐碎的SQL一次一个地检查我的参数，只是为了排除它们可能是罪魁祸首。类似这样的东西：

var result = db.Query("select r=cast(@0 as varchar(80))",username);
var result = db.Query("select r=cast(@0 as int)",newGroupID);
var result = db.Query("select r=cast(@0 as datetime)",start);
var result = db.Query("select r=cast(@0 as datetime)",end);
相关文章：