mysql count和where_MySQL加入count和where(MySQL join with count and where)

MySQL加入count和where(MySQL join with count and where)

我想获取所有讨论并添加所有UserDiscussion行的计数，其中列'bookmarked'对于讨论是真的。

Table: Discussion

-----------------

DiscussionID

Name

.....

Table: UserDiscussion

---------------------

UserID

DiscussionID

Bookmarked (Boolean)

这是我的查询，但不正确：

SELECT d.*, COUNT(*) as BookmarkCount

FROM Discussion d

LEFT JOIN UserDiscussion ud

ON ud.DiscussionID = d.DiscussionID

WHERE ud.Bookmarked = true;

I want to fetch all discussions and add the count of all UserDiscussion rows where column 'bookmarked' is true for the discussion.

Table: Discussion

-----------------

DiscussionID

Name

.....

Table: UserDiscussion

---------------------

UserID

DiscussionID

Bookmarked (Boolean)

This is my query but it is not correct:

SELECT d.*, COUNT(*) as BookmarkCount

FROM Discussion d

LEFT JOIN UserDiscussion ud

ON ud.DiscussionID = d.DiscussionID

WHERE ud.Bookmarked = true;

原文：https://stackoverflow.com/questions/37950680

更新时间：2019-09-07 14:11

最满意答案

写这个的正确方法是：

SELECT d.*, COUNT(ud.DiscussionID) as BookmarkCount

FROM Discussion d LEFT JOIN

UserDiscussion ud

ON ud.DiscussionID = d.DiscussionID AND

ud.Bookmarked = true

GROUP BY d.DiscussionId;

变化是：

在第二个表的字段上使用COUNT() 。 这将计算匹配数。 在这种情况下， COUNT(*)将永远不会返回0 (该组将被过滤掉)。

将WHERE条件移动到ON子句。 否则， WHERE条件将过滤掉NULL值，将LEFT JOIN转换为内部LEFT JOIN 。

添加GROUP BY子句，以便每个讨论ID获得一行。

注意：通过DiscussionId分组很好，假设它是Discussion上的主键。 通常，最好在GROUP BY中的SELECT中包含所有未聚合的列。 但是，当GROUP BY是主/唯一键时，它是可以的(并且符合ANSI SQL)。

The proper way to write this is:

SELECT d.*, COUNT(ud.DiscussionID) as BookmarkCount

FROM Discussion d LEFT JOIN

UserDiscussion ud

ON ud.DiscussionID = d.DiscussionID AND

ud.Bookmarked = true

GROUP BY d.DiscussionId;

The changes are:

Using COUNT() on a field from the second table. This will count the number of matches. COUNT(*) will never return 0, in this case (the group would be filtered out).

Moving the WHERE condition to the ON clause. Otherwise, the WHERE condition would filter out NULL values, turning the LEFT JOIN into an inner join.

Adding a GROUP BY clause so you get one row per discussion id.

Note: Grouping by DiscussionId is fine, assuming that it is the primary key on Discussion. In general, it is better to include all unaggregated columns in the SELECT in the GROUP BY. But it is okay (and compliant with ANSI SQL), when the GROUP BY is by a primary/unique key.

2016-06-21

相关问答

GROUP BY是正确的路要走，所以只需添加： GROUP BY t.thread_id GROUP BY was the right way to go, so just add: GROUP BY t.thread_id

我可以说这是(至少)我今天改进的最酷的选择陈述之一。 SELECT STRAIGHT_JOIN

t.* ,

COUNT( DISTINCT b.comment_id ) AS comments,

COUNT( DISTINCT bt.follow_id ) AS followers,

COUNT( DISTINCT c.fan_id ) AS fans

FROM

(

SELECT * FROM talks

WHERE privacy = 'publ

...

写这个的正确方法是： SELECT d.*, COUNT(ud.DiscussionID) as BookmarkCount

FROM Discussion d LEFT JOIN

UserDiscussion ud

ON ud.DiscussionID = d.DiscussionID AND

ud.Bookmarked = true

GROUP BY d.DiscussionId;

变化是： 在第二个表的字段上使用COUNT() 。 这将计算匹配数。 在这

...

这应该工作： SELECT

markers.name

,markers_date.id

,markers_date.markers_id

,markers_date.date

,markers_date.date2

,markers_date.time

,registration.schedule_id

,COUNT(registration.schedule_id) AS COUNT

FROM markers_date

JO

...

你的第二个查询很好。 只需由第一个表中的用户分组即可。 否则，你会将你的left join变成一个inner join SELECT

`user`.`name`,

COUNT(`user_rh`.`uid`) AS `count`

FROM

`user`

LEFT JOIN

`user_rh`

ON (`user_rh`.`uid`=`user`.`uid`)

GROUP BY

`user`.uid, `user`.`name`

ORDER BY

...

COUNT(X)计算它遇到的X值的总数不为空，COUNT(DISTINCT X)应该给你你想要的东西(假设X是一个唯一的字段)。 COUNT(X) counts the total number of X values it comes across that are not null, COUNT(DISTINCT X) should give you what you want (assuming X is a unique field).

您需要使用子查询来选择使用两个slugs的adv_id ： SELECT COUNT(DISTINCT advert.id) as count, region.id as reg_id

FROM region

JOIN advert ON region.id = advert.region_id

JOIN (SELECT adv_use.adv_id

FROM adv_use

JOIN tbl_use ON adv_use.use_id = tbl_use.id

...

您按code.id并在每个组中计数(DISTINCT comments.codeid) ，但是在JOIN中定义的comments.codeid = code.id ，这就是为什么总是得到1。 您需要通过评论中的其他字段计算...如果有主要代理键，这是COUNT(comments.commentid) 。 此外，如果已知每个组中的注释都是不同的，则应使用简单的COUNT(*) 。 You group by code.id and in each group you count (DISTINCT c

...

在这种情况下，你绝对不应该加入约会。 例如，您可能拥有以下数据： deskcases ringcentral

--------- -----------

date | whatever_d date | whatever_r

---------------------- ----------------------

toda

...