SQL存储过程

Question

我有3个表：

• tbl_Image —— 从中获取所有图像列表
• 一个 user 表 —— 从中获取用户 ID
• 以及一个名为 tbl_MemberAssociation 的 Image 和 Member 关联表。

我的工作流程是，用户可以上传图像并将其存储到图像表中。然后所有用户都可以查看此图像，并选择提供的三个选项之一。如果用户选择选项，它将被添加到关联表中。没有用户可以观看同一图像超过一次。因此不会有多个条目。

现在，我想通过获取选择了相同选项和不同选项的成员列表来找到匹配度的百分比，与他们提供了选项的所有共同图像相对应。

例如，如果三个用户 A、B 和 C 查看了泰姬陵的一张图片。如果 A 和 B 选择了“美丽”作为选择，而 C 则选择了“不好”。对于另一张图片，比如印度国旗，A、B 和 C 都选择了“敬礼”。那么对于用户 A：B 的匹配度为100%（因为他们两次都选择了相同的选项）。对于 A：C 来说，其中一半与 B 相同，所以匹配度为50%。

这就是我的情况，在其中我必须查找所有与当前登录用户匹配的内容。

请帮帮我……我对这个过程完全感到困扰。

- Prem Singh

---

你使用的是哪个数据库系统和哪个版本？存储过程代码通常在不同供应商之间不是标准化的... - marc_s

我理解的对吗：您想要一个存储过程，它以两个用户ID作为输入，并返回这两个用户的答案有多少相同的百分比？如果一个用户已经给出了答案而另一个用户没有呢？这算作“不同的答案”吗？还是跳过该问题（因为并非所有用户都回答了它）？ - marc_s

我想使用SQL Server 2008。 - Prem Singh

Answer 1

我已经对你的表格实际结构做出了一些假设，但如果我理解你的需求，那么我认为这个查询将得到你想要的结果。你可能需要进行一些修改以匹配你的表格结构。

    SELECT
        matches.UserName,
        CAST(matches.SameRatings AS FLOAT) / CAST(ratings.UserRatingCount AS FLOAT) AS MatchPercent
    FROM 
        tbl_User
    CROSS APPLY
    (
        SELECT
            COUNT(*) UserRatingCount
        FROM 
            tbl_MemberAssociation
        WHERE 
            UserId = tbl_User.UserId
    ) ratings
    CROSS APPLY
    (
        SELECT
            u1.UserId,
            u1.UserName,
            COUNT(*) AS SameRatings
        FROM 
            tbl_MemberAssociation ma
        INNER JOIN 
            tbl_MemberAssociation ma1 
        ON
            ma.ImageId = ma1.ImageId 
        AND ma.Rating = ma1.Rating 
        AND ma.UserId <> ma1.UserId
        INNER JOIN 
            tbl_User u1 
        ON 
            ma1.userId = u1.UserId
        WHERE 
            ma.UserId = tbl_User.UserId
        GROUP BY
            u1.UserId,
            u1.UserName
    ) matches
    WHERE
        tbl_User.UserId = @UserId
    ORDER BY
        MatchPercent DESC

@UserId 可以作为输入传递给存储过程。

第一个 CROSS APPLY "ratings" 获取登录用户的总评分数。

第二个 CROSS APPLY "matches" 获取数据库中其他用户的喜欢评分数。

结果集使用两个 CROSS APPLY 查询计算的计数来计算登录用户和已评价与登录用户相同图像的其他用户之间的匹配百分比。

- Justin N