MySql:如何进行子查询并计算两个表中id相同的所有行 [英] MySql: how to make subquery and count all rows where id is the same in two tables

问题描述

如何进行查询，而不是针对所有特定 ID 返回值.

How to make a query which return values for specific ID's not for all.

    SELECT content.id, count(case likes.type when 'p' then 1 else null end) as p
FROM content
JOIN likes on likes.content_id = content.id

此代码返回:

ID p
1 18

但我想要:

ID p
1 12
2 4
3 2

推荐答案

您的查询在大多数数据库中都会失败.问题是 content.id 未在 select 中汇总，但您使用的是聚合函数.

You query would fail in most databases. The problem is that content.id is not summarized in the select but you are using an aggregation function.

这是一个需要解决的简单问题:

This is a simple problem to fix:

SELECT content.id, count(case likes.type when 'p' then 1 else null end) as p
FROM content
JOIN likes on likes.content_id = content.id
GROUP BY content.id;

但是，一般来说，您应该小心，并且始终将 select 中的所有非聚合列包含在 group by 中.

However, in general, you should be careful and always include all non-aggregated columns in the select in a group by.

这篇关于MySql:如何进行子查询并计算两个表中id相同的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！