具有连接数和案例数的 Mysql 查询 - 添加更多连接和子查询

Question

到目前为止我得到了这个:

SELECT connections.id,
       connections.word_id,
       connections.order_id,
       connections.top,
       connections.deleted,
       (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(connections.modified)) AS modified_ago,
       words_en.word,
       (CASE WHEN words_en.user_id = 1 THEN "You" ELSE words_en.user_id END) AS created_by
FROM connections, words_en
WHERE connections.word_id = words_en.id AND connections.user_id = 1
ORDER BY connections.order_id

我想添加一些东西，但语法不正确。

1. (CASE WHEN words_en.user_id = 1 THEN "You"ELSE words_en.user_id END) 在这里，当 ELSE 时，我想得到 name_surname users 表中此用户的列。所以我需要再加入一次？但既然是在 case 之内，我该怎么做呢？

来自 SELECT 的

2. connections.word_id。除了 ID，我还想知道这个 word_id 在连接表中出现了多少次。但从列表中排除当前用户。

我希望解释足够清楚。

Answer 1

首先，使用 ANSI 连接重写您的查询，并添加表别名，如下所示:

SELECT c.id,
       c.word_id,
       c.order_id,
       c.top,
       c.deleted,
       (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(c.modified)) AS modified_ago,
       words_en.word,
       (CASE WHEN words_en.user_id = 1 THEN "You" ELSE w.user_id END) AS created_by
FROM connections c
JOIN words_en w ON c.word_id = w.id
WHERE c.user_id = 1
ORDER BY c.order_id

现在扩展此查询变得更容易:通过 w.user_id 引入用户，添加另一个连接:

SELECT c.id,
       c.word_id,
       c.order_id,
       c.top,
       c.deleted,
       (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(c.modified)) AS modified_ago,
       words_en.word,
       (CASE WHEN words_en.user_id = 1 THEN "You" ELSE u. name_surname END) AS created_by
FROM connections c
JOIN words_en w ON c.word_id = w.id
JOIN users u ON w.user_id = u.id
WHERE c.user_id = 1
ORDER BY c.order_id

要添加计数，请使用子查询，如下所示:

SELECT c.id,
       c.word_id,
       c.order_id,
       c.top,
       c.deleted,
       (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(c.modified)) AS modified_ago,
       words_en.word,
       (CASE WHEN words_en.user_id = 1 THEN "You" ELSE u. name_surname END) AS created_by,
       (
           SELECT COUNT(*)
           FROM connections cc
           WHERE cc.word_id=c.word_id     -- It's the same word
             AND cc.user_id <> c.user_id  -- user by a different user
       ) as uses_by_others
FROM connections c
JOIN words_en w ON c.word_id = w.id
JOIN users u ON w.user_id = u.id
WHERE c.user_id = 1
ORDER BY c.order_id