mysql - 无法从查询中获取适当的值？

Question

我想列出 companyIds 以及最常出现的可评论类型 (0,1,2)。

这是子查询

select a.companyId, a.commentable, count(1) _count
from article a
group by a.companyId, a.commentable

| companyId | commentable | _count |
|-----------|-------------|--------|
|         1 |           0 |      1 |
|         1 |           1 |      1 |
|         2 |           0 |   7759 |
|         2 |           1 |   7586 |
|         2 |           2 |   7856 |
|         3 |           0 |   7828 |
|         3 |           1 |   7866 |
|         3 |           2 |   7706 |
|         4 |           0 |   7851 |
|         4 |           1 |   7901 |
|         4 |           2 |   7738 |
|         5 |           0 |   7775 |
|         5 |           1 |   7884 |
|         5 |           2 |   7602 |
|        25 |           0 |   7888 |
|        25 |           1 |   7939 |
|        25 |           2 |   7784 |

例如上面companyId=4 出现的大多数可评论类型是 7901，可评论类型是 1。在下面的查询中，我看到 4-0-7901，但我期望 4-1-7901

 SELECT x.companyId, x.commentable, MAX(x._count) _count
FROM 
(   SELECT a.companyId, a.commentable, COUNT(1) _count
    FROM article a
    GROUP BY a.companyId, a.commentable
) AS X
GROUP BY x.companyId;


companyId   commentable _count
1   0   1
2   0   7856
3   0   7866
4   0   7901
5   0   7884
25  0   7939

Expected result
companyId   commentable _count
    1   0   1
    2   2   7856
    3   1   7866
    4   1   7901
    5   1   7884
    25  1   7939

我不明白'为什么所有可评论列都是'0'。

Answer 1

你需要一个丑陋的连接。在下面的查询中，您可以查看关于公司的 GROUP BY 查询，并注释输入基本工作单元。此查询本身出现，别名为 t1。在别名 t2 中，我们仅通过 commentable 进行子查询和聚合，以找到每种此类评论类型的最大计数。为此，我们加入 t1 以仅限制拥有最大数量的公司。

SELECT
    t1.companyId,
    t1.commentable,
    t1.cnt
FROM
(
    SELECT companyId, commentable, COUNT(*) cnt
    FROM article
    GROUP BY companyId, commentable
) t1
INNER JOIN
(
    SELECT companyId, MAX(cnt) max_cnt
    FROM
    (
        SELECT companyId, commentable, COUNT(*) cnt
        FROM article
        GROUP BY companyId, commentable
    ) t
    GROUP BY companyId
) t2
    ON t1.companyId = t2.companyId AND t1.cnt = t2.max_cnt;

顺便说一句，在 MySQL 8+ 中，事情变得更好了，我们可以在其中利用分析函数:

WITH cte AS (
    SELECT companyId, commentable, COUNT(*) cnt,
        ROW_NUMBER() OVER (PARTITION BY commentable ORDER BY COUNT(*) DESC) rn
    FROM article
    GROUP BY companyId, commentable
)

SELECT companyId, commentable, cnt
FROM cte
WHERE rn = 1;