SQL-使用另一个表中的条件执行SELECT

Question

我有四个表：user、game、user_games_joined和user_rating。

用户可以为另一个用户打分，但前提是自上次为该用户打分以来，他们与该用户一起玩过游戏（即user_games_joined表包含同一游戏中每个用户的行）。

我试图构建一个SELECT语句，告诉我们当前是否允许用户为另一个特定用户打分。到目前为止，我拥有的是：

SELECT COUNT(*)
FROM user_games_joined from_joined
LEFT JOIN game ON from_joined.game_uuid = game.uuid
INNER JOIN user_games_joined to_joined ON from_joined.game_uuid = to_joined.game_uuid
WHERE from_joined.user_uuid = [USER_GIVING_RATING_UUID]
AND to_joined.user_uuid = [USER_RECEIVING_RATING_UUID]
AND game.date > [DATE_OF_MOST_RECENT_RATING];

如果此语句返回0，则最终答案为false；elsetrue（自上次评分以来，两个用户之间至少玩了一场游戏）。问题是[DATE_OF_MOST_REQUENT_RATION]未知：它必须从user_rating表中确定。以下是我们查询该信息的方法：

SELECT date from user_rating
WHERE to_user_uuid = [USER_RECEIVING_RATING_UUID]
AND from_user_uuid = [USER_GIVING_RATING_UUID]
ORDER BY date DESC LIMIT 1;

因此，我当然可以用两个单独的查询来完成这项任务，但出于显而易见的原因，我希望将其合并为一个语句。有人能帮我吗？谢谢

EDIT:对于奖励积分，有人能想出一种方法在插入user_rating表的过程中执行此检查吗，这样验证就不需要在插入之前作为单独的查询进行？

Answer 1

这是一组相当复杂的带有一些聚合的联接：

select (case when coalesce(max(ur.rating_date), max(g.date)) <= max(g.date) then 'Go and Rate'
             else 'No, no, no.  Go and play'
        end)
from user_game g join
     user_games_joined ufrom 
     on ufrom.game_uuid = g.uuid and
        ufrom.user_uuid = g.from_user_uuid join
     user_games_joined uto
     on uto.game_uuid = g.uuid and
        uto.user_uuid = g.to_user_uuid left join
     user_rating ur join
     on ur.from_user_uuid = ufrom.user_uuid and
        ur.to_user_uuid = uto.user_uid
where to_user_uuid = [USER_RECEIVING_RATING_UUID] and
      from_user_uuid = [USER_GIVING_RATING_UUID];

出于您的目的，更好的select是：

select (max(ur.rating_date) < max(g.date))

Answer 2

编写一个通用查询，因为我不知道您使用的是哪个数据库。我也没有测试过，所以可能会有一些错误，但你应该从下面得到它的要点。您可能需要调整查询的语法和性能：

INSERT INTO user_rating
     SELECT new_rating FROM DUAL
            WHERE EXISTS (
                  SELECT 1 --selecting 1 IS FASTER THAN COUNT(*)
                       FROM user_games_joined from_joined
                           LEFT JOIN game 
                               ON from_joined.game_uuid = game.uuid
                           INNER JOIN user_games_joined to_joined 
                               ON from_joined.game_uuid = to_joined.game_uuid
                           INNER JOIN user_rating XX
                               ON XX.to_user_uuid = to_joined.user_uuid
                                  AND XX.from_user_uuid = from_joined.user_uuid
                                  AND Game.date > XX.date 
                       WHERE from_joined.user_uuid = [USER_GIVING_RATING_UUID]
                           AND to_joined.user_uuid = [USER_RECEIVING_RATING_UUID]
                           AND game.date > [DATE_OF_MOST_RECENT_RATING];
                         );