加入两个表的mysql，一对多的关系 [英] Join two tables mysql, one to many relationship

问题描述

我有两个表:

点->

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   

和项目->

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   

我希望将它们加入一个查询中，这样我将得到item.*以及相对于它们的总点数.我也只想对过去24小时内为其创建了任何积分的商品执行此操作.

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.

推荐答案

您需要使用GROUP BY来说明要基于哪些分组.如您所见，如果没有GROUP BY，您只会得到整个结果集中的单个组.

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.

在标准SQL中，有必要在GROUP BY子句中包含SELECT子句中包含的每个非聚合表达式，但是MySQL允许您不必这样做，可以使用如下表达式. (至少，在非严格模式下；我不确定严格模式是否会增强此要求以匹配标准SQL)

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`

假定items.id是此表的主键，因此它不会出现在items的多行中，这应该具有预期的效果.

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.

一旦您介绍了GROUP BY，了解WHERE和HAVING子句之间的区别就很重要.前者适用条件 before ，而聚合适用，而后者适用 after .这意味着如果要基于该计数执行条件运算，则必须使用HAVING；否则，必须使用HAVING.您最初示例中的WHERE子句将在聚合之前应用，因此结果将是在给定日期之后创建的点数.

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.

这篇关于加入两个表的mysql，一对多的关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！