无法从查询中获取适当的值?

dwbf0jvd  于 2021-06-17  发布在  Mysql
关注(0)|答案(3)|浏览(265)

我想列出companyID和最常出现的可注解类型(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,而companyid=4的可注解类型最多为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'。

cfh9epnr

cfh9epnr1#

原因 commentable 不是其中之一 group by 柱。在这种情况下 ONLY_FULL_GROUP_BY 禁用后,mysql可以为该列自由选择任何一个值。
来自mysql文档
如果仅禁用了\u full \u group by,则groupby的标准sql使用的mysql扩展允许select list、having condition或order by list引用未聚合的列,即使这些列在功能上不依赖于groupby列。这使mysql接受前面的查询。在这种情况下,服务器可以自由地从每个组中选择任何值,因此除非它们相同,否则选择的值是不确定的,这可能不是您想要的。

z9smfwbn

z9smfwbn2#

你需要一个丑陋的大联盟。在下面的查询中,您可以查看 GROUP BY 查询公司并输入基本工作单元的注解。此查询显示为自身,别名为 t1 . 在alias中 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;
qnakjoqk

qnakjoqk3#

你可以使用 having 条款:

SELECT a.companyId, a.commentable, COUNT(*) as _count
FROM article a
GROUP BY a.companyId, a.commentable
HAVING COUNT(*) = (SELECT COUNT(*)
                   FROM article a2
                   WHERE a2.companyId = a.companyId
                   GROUP BY a2.commentable
                   ORDER BY COUNT(*) DESC
                   LIMIT 1
                  );

如果出现并列,您将得到多行。如果您希望每个公司只有一行,那么可以使用 commentable 为了比较 HAVING :

SELECT a.companyId, a.commentable, COUNT(*) as _count
FROM article a
GROUP BY a.companyId, a.commentable
HAVING a.commentable = (SELECT a2.commentable
                        FROM article a2
                        WHERE a2.companyId = a.companyId
                        GROUP BY a2.commentable
                        ORDER BY COUNT(*) DESC
                        LIMIT 1
                       );

正如其他人提到的,你的问题是错误使用 GROUP BY . 中未聚合的列 SELECT 需要匹配 GROUP BY 钥匙——反之亦然。

相关问题