mysql - 在 MariaDB 中的两个表选择语句中对第三个表使用计数函数

Question

我只是花了几个小时通读了 MariaDB 文档和这里的各种问题，试图找出满足我想要的 SQL 语句。我绝对不是专家......最终我确实得到了我预期的结果，但我不知道它为什么有效。我想确保我确实得到了我想要的结果，而且它不仅仅适用于我抛出的少数测试用例。
我有三张表留言簿、用户和 user_likes。我正在尝试编写一个 SQL 语句，该语句将返回用户的用户名和名字、帖子内容、帖子日期、留言簿中的帖子 ID，以及第三列喜欢，这是留言簿中的帖子 ID 出现的总次数user_likes 表。它应该只返回标准类型的帖子，并且应该按升序发布日期对行进行排序。
样本数据:

CREATE TABLE users
    (`user_id` int, `user_first` varchar(6), `user_last` varchar(7),
     `user_email` varchar(26), `user_uname` varchar(6))
;
    
INSERT INTO users
    (`user_id`, `user_first`, `user_last`, `user_email`, `user_uname`)
VALUES
    (0, 'Bob', 'Abc', 'email@example.com', 'user1'),
    (13, 'Larry', 'Abc', 'email@example.com', 'user2'),
    (15, 'Noel', 'Abc', 'email@example.com', 'user3'),
    (16, 'Kate', 'Abc', 'email@example.com', 'user4'),
    (17, 'Walter', 'Sobchak', 'walter.sobchak@shabbus.com', 'Walter'),
    (18, 'Jae', 'Abc', 'email@example.com', 'user5')
;


CREATE TABLE user_likes
    (`user_id` int, `post_id` int, `like_id` int)
;
    
INSERT INTO user_likes
    (`user_id`, `post_id`, `like_id`)
VALUES
    (0, 23, 1),
    (0, 41, 2),
    (13, 23, 7)
;


CREATE TABLE guestbook
    (`post_id` int, `user_id` int, `post_date` datetime,
     `post_content` varchar(27), `post_type` varchar(8),
     `post_level` int, `post_parent` varchar(4))
;
    
INSERT INTO guestbook
    (`post_id`, `user_id`, `post_date`, `post_content`,
     `post_type`, `post_level`, `post_parent`)
VALUES
    (2, 0, '2018-12-15 20:32:40', 'test1', 'testing', 0, NULL),
    (8, 0, '2018-12-16 14:06:40', 'test2', 'testing', 0, NULL),
    (9, 13, '2018-12-16 15:47:55', 'test4', 'testing', 0, NULL),
    (23, 0, '2018-12-25 17:59:46', 'Merry Christmas!', 'standard', 0, NULL),
    (39, 16, '2018-12-26 00:28:04', 'Hello!', 'standard', 0, NULL),
    (40, 15, '2019-01-27 00:46:12', 'Hello 2', 'standard', 0, NULL),
    (41, 18, '2019-02-25 00:44:35', 'What are you doing?', 'standard', 0, NULL)
;

我尝试了一大堆涉及 count 的复杂语句，但没有得到我想要的。通过看似愚蠢的运气，我偶然创建了这个声明，它似乎给了我想要的东西。

SELECT 
  u.user_uname, u.user_first, g.post_id, g.post_date,
  g.post_content, count(user_likes.post_id) AS likes
FROM
  users AS u, guestbook AS g
LEFT JOIN
  user_likes on g.post_id=user_likes.post_id
WHERE
  u.user_id=g.user_id AND g.post_type='standard'
GROUP BY
  g.post_id
ORDER BY
  g.post_date ASC;

题:
为什么这个计数功能似乎有效？
我能够使用的计数函数是这样的，但它仅适用于硬编码的 post_id 值。

SELECT COUNT(CASE post_id WHEN 23 THEN 1 ELSE null END) FROM user_likes;

当我尝试通过更改为这个来匹配留言簿表中的 post_id 时，我得到一个错误的值，它似乎是整个 user_likes 表。

SELECT COUNT(case when guestbook.post_id=user_likes.post_id then 1 else null end) FROM guestbook, user_likes;

添加 GROUP BY guestbook.post_id最后让我更接近，但现在我需要弄清楚如何将它与我原来的选择语句结合起来。

+----------------------------------------------------------------------------+
| COUNT(case when guestbook.post_id=user_likes.post_id then 1 else null end) |
+----------------------------------------------------------------------------+
|                                                                          0 |
|                                                                          0 |
|                                                                          0 |
|                                                                          2 |
|                                                                          0 |
|                                                                          0 |
|                                                                          1 |
+----------------------------------------------------------------------------+

这是我想要的输出，我得到了。我只是不相信我的陈述是可靠的或正确的。

+------------+------------+---------+---------------------+---------------------+-------+
| user_uname | user_first | post_id | post_date           | post_content        | likes |
+------------+------------+---------+---------------------+---------------------+-------+
| user1      | Bob        |      23 | 2018-12-25 17:59:46 | Merry Christmas!    |     2 |
| user4      | Kate       |      39 | 2018-12-26 00:28:04 | Hello!              |     0 |
| user3      | Noel       |      40 | 2019-01-27 00:46:12 | Hello 2             |     0 |
| user5      | Jae        |      41 | 2019-02-25 00:44:35 | What are you doing? |     1 |
+------------+------------+---------+---------------------+---------------------+-------+

声明工作的 fiddle : http://sqlfiddle.com/#!9/968656/1/0

Answer 1

JOIN + COUNT -- 查询首先按照 JOIN 的指示组合表和 ON条款。结果被(至少在逻辑上)放入一个临时表中。通常，此临时表的行比 JOINed 中的任何表都多得多。 .
然后是COUNT(..)被执行。它正在计算该临时表中的行数。也许这个数字正是你想要的，也许它是一个非常膨胀的数字。count(user_likes.post_id)有一个额外的问题，即不计算任何行 user_likes.post_id IS NULL .这通常无关紧要，在这种情况下，您应该简单地说 COUNT(*) .
请不要使用逗号列表形式加入。始终使用 FROM a JOIN b ON ...哪里ON子句说如何表 a和 b有关系。如果还有一些过滤，请将其放入 WHERE条款。
如果COUNT太大了，把你开发的查询放在一边，重新开始开发一个只做一件事的查询——计算县。此查询可能会使用较少的表。
然后在此基础上获取您需要的任何其他数据。它可能看起来像

SELECT ...
    FROM ( SELECT foo, COUNT(*) AS ct FROM t1 GROUP BY foo ) AS sub1
    JOIN t2 ON t2.foo = sub1.foo
    JOIN t3 ON ...
    WHERE ...

获取正确的初始查询 COUNT .然后，如果需要，请回来寻求更多帮助。
正如布莱恩所尝试的那样
好的，我做了一些更改。

SELECT  u.user_uname, u.user_first,
        g2.post_id, g2.post_content, g2.post_date,
        sub.likes
    FROM  
    (
        SELECT  g.post_id,
                SUM(g.post_id = ul.post_id) AS likes
            FROM  guestbook AS g
            JOIN user_likes AS ul
            WHERE  g.post_type = 'standard'
    ) AS sub
    JOIN  guestbook AS g2  ON sub.post_id = g2.post_id
    JOIN  users AS u       ON u.user_id = g2.user_id;

索引:

guestbook:  (post_type, post_id)  -- for derived table
guestbook:  (post_id)             -- for outer SELECT
users:  (user_id)
user_likes:  (post_id)
    

笔记:

ORDER BY删除，因为它在上下文中是无用的。

COUNT..CASE改为较短 SUM .

JOIN ON二手

由于派生表中只有一个值，因此这可能同样有效:

SELECT  u.user_uname, u.user_first,
        g.post_id, g.post_content, g.post_date,
        ( SELECT  COUNT(*)
            FROM user_likes AS ul
            WHERE g.post_id = ul.post_id
        ) AS likes
    FROM  guestbook AS g
    JOIN  users AS u   USING(user_id);
    WHERE g.post_type = 'standard'

这涉及很多变化；看看它是否看起来“正确”。现在简单了很多。
索引与上述相同。