sql - 如何修改 LEFT OUTER JOIN 以添加右侧表的过滤器

Question

我的数据库中有三个表。

• AdminTest - 包含可供用户使用的测试列表
• AdminTestQuestion - 包含问题列表
• UserTest - 包含用户已购买的测试列表。此表中有一个 UserId 列，表中的行始终具有该列的值。进行选择时，我需要能够按 UserId 过滤掉此表中的行

数据如下所示:

• 数据库存储了三个测试的结果。测试 1、测试 2 和测试 3
• userId = 1 的人购买了 test2
• userId = 2 的人购买了 test3。

我正在使用以下 SQL:

SELECT 
    AdminTest.AdminTestId,
    AdminTest.Title,
    COUNT(AdminTestQuestion.AdminTestQuestionId) Questions,
    AdminTest.Price,
    UserTest.PurchaseDate
FROM AdminTest
    LEFT OUTER JOIN UserTest
        ON AdminTest.AdminTestId = UserTest.AdminTestId
    JOIN AdminTestQuestion
        ON AdminTest.AdminTestId = AdminTestQuestion.AdminTestId
GROUP BY 
    AdminTest.AdminTestId,
    AdminTest.Title,
    UserTest.UserId

这给了我这样的报告:

AdminTestId  Title    Questions     Price     PurchaseDate
1            Test1    10            0
2            Test2    20            0         1/1/2011
3            Test3    10            10        2/2/2012

有人可以建议我如何修改它，以便 SQL 采用 UserId 参数，以便它可以正确显示特定用户已购买的测试:

当我为 UserId 参数提供值 1 时，这是我希望看到的:

AdminTestId  Title    Questions     Price     PurchaseDate
1            Test1    10            0
2            Test2    20            0         1/1/2011  
3            Test3    10            10     

当我为 UserId 参数提供值 2 时，这是我希望看到的:

AdminTestId  Title    Questions     Price     PurchaseDate
1            Test1    10            0
2            Test2    20            0         
3            Test3    10            10        2/2/2012

到目前为止，我尝试的是将带有 UserId 的 WHERE 子句添加到选择的 AdminUser 部分。但这似乎不起作用。我希望有人能指出我正确的方向。

这里是我想用 UserId 以某种方式过滤掉的 UserTest 表的 DDL 作为引用:

CREATE TABLE [dbo].[UserTest] (
    [UserTestId]              INT           IDENTITY (1, 1) NOT NULL,
    [AdminTestId]             INT           NOT NULL,
    [UserId]                  INT           NOT NULL,
    [PurchaseDate]            DATETIME      NOT NULL,
    CONSTRAINT [PK_UserTest] PRIMARY KEY CLUSTERED ([UserTestId] ASC)
);

Answer 1

扩展@RichardHansell 所说的......

您可以通过向脚本的“ON”子句添加内容来过滤 JOIN。 ON 子句不必只是两个表之间的链接，您也可以添加其他过滤器。像这样...

SELECT   AdminTest.AdminTestId,
         AdminTest.Title,
         COUNT(AdminTestQuestion.AdminTestQuestionId) Questions,
         AdminTest.Price,
         UserTest.PurchaseDate
FROM     AdminTest 
LEFT OUTER JOIN UserTest
ON       AdminTest.AdminTestId = UserTest.AdminTestId 
AND      UserTest.UserId = @FilteredUserId
JOIN AdminTestQuestion
ON       AdminTest.AdminTestId = AdminTestQuestion.AdminTestId
GROUP BY AdminTest.AdminTestId, AdminTest.Title, UserTest.UserId