mysql 1对多关系图,加入两个表mysql，一对多关系

I have two tables:

Points ->

id bigint(20) NO PRI NULL auto_increment

created_at datetime NO NULL

ip varchar(255) NO NULL

item_id bigint(20) NO MUL NULL

updated_at timestamp YES NULL

and Items ->

id bigint(20) NO PRI NULL auto_increment

author varchar(255) NO NULL

created_at datetime NO NULL

description varchar(255) NO NULL

link varchar(255) NO NULL

source varchar(255) NO NULL

title varchar(180) NO NULL

url_encoded varchar(255) NO UNI NULL

updated_at timestamp YES NULL

I want to join them hopefully in one query so I will get item.* and the total of how many points are relative to them. I also want to do this only for the items that has any points created for them in the last 24 hours.

This is my query so far:

SELECT `items`.*, COUNT(points.item_id) as points

FROM `items`

INNER JOIN `points` ON `items`.`id` = `points`.`item_id`

WHERE `points`.`created_at` > '2013-03-16 16:00:14'

ORDER BY points DESC

LIMIT 30;

Unfortunately it gives me only one row when it should be two with two points when it should be one. In my database there is two items and one point for each of them. Please help me fix this and understand how I can improve my query to get both the results.

解决方案

You need to use GROUP BY to explain what groupings to count based on. Without GROUP BY you just get a single group of the entire result set, as you saw.

In standard SQL it is necessary to include in the GROUP BY clause every non-aggregate expression that's included in the SELECT clause, but MySQL lets you get away with not doing this, allowing an expression like the following. (At least, when not in strict mode; I'm not sure if strict mode strengthens this requirement to match standard SQL)

SELECT `items`.*, COUNT(1) AS points

FROM `items` INNER JOIN `points` ON `items`.`id` = `points`.`item_id`

WHERE ...

GROUP BY `items`.`id`

Assuming that items.id is the primary key of this table, and so it won't appear in more than one row of items, this should have the desired effect.

Once you introduce GROUP BY it's important to understand the difference between the WHERE and HAVING clauses. The former applies the condition before the group and aggregates are applied, while the latter applies afterwards. This means you must use HAVING if you want to do a conditional based on that count; the WHERE clause in your initial example will apply before the aggregate, so the result will be the count of points created after the given date.