mysql - 在自身内部使用自己的结果的 SQL 查询

Question

对于我试图用 mysql 编写的这个查询，我们将不胜感激。我有两个表:

files (id, name, subject, size, day, hour, username) and 
friendships (user1, user2). 

我想从表 files 中选择 10 最近的 行(根据 id)，其中每一行的 username 是出现在表 friendships 中，friendships 的第二列 等于某个 name 'x'。

我正在尝试使用这个语句:

SELECT name, 
       subject, 
       size, 
       day, 
       hour, 
       username 
FROM   files 
WHERE  username IN ((SELECT user1 
                     FROM   friendships 
                     WHERE  user2 = 'x') 
                    UNION ALL 
                    (SELECT user2 
                     FROM   friendships 
                     WHERE  user1 = 'x')) 
ORDER  BY id DESC 
LIMIT  10;

但是，我得到一个错误。这有什么问题，有没有更好的方法？

Answer 1

我不确定是什么导致了您的错误，但有更好的方法。将其拆分为两个操作。我通常使用 exists(尽管 in 可能没问题):

WHERE EXISTS (SELECT 1 FROM friendships fs WHERE fs.user2 = 'x' and fs.user1 = files.username) OR
      EXISTS (SELECT 1 FROM friendships fs WHERE fs.user1 = 'x' and fs.user2 = files.username) 

这个公式将允许查询更好地利用索引，特别是在 friendships(user1, user2) 和 friendships(user2, user1) 上(是的，两者都是在这种情况下是可取的)。