如何在一个表中连接具有多个行的两个表?

Let's say I have:

假设有:

USERS:
userid | name
1      | John
2      | Jack

HITS:
id | userid | time
1  | 1      | 50
2  | 1      | 51
3  | 2      | 52
4  | 1      | 53
5  | 2      | 54
6  | 2      | 55

I need to end up with a structure like this:

我需要一个这样的结构:

array() {
  [user 1] {
    hit 1 => 50
    hit 2 => 51
    hit 4 => 53
  }
  [user 2] {
    hit 3 => 52
    hit 5 => 54
    hit 6 => 55
  }
}

The worst possible way to do this is to:

最糟糕的做法是:

Select * users
Select *用户
Select all hits for each users
为每个用户选择所有的点击数。

Is there a way to get a result as one single query without going through each like this?

是否有一种方法可以在不像这样遍历每个查询的情况下得到一个结果?

4 个解决方案

#1

Use Left Outer Join, Retrieve All records from left table(main) and other matching from right table.

使用左外连接，从左表(主表)检索所有记录，从右表检索其他匹配。

select 
users.userid,  
h.time 
from  
users  
    left outer join hits h   
    on(  
      users.userid = h.userid
    )

#2

Yes, you can use a JOIN for this.

是的，你可以用一个连接。

SELECT * FROM users
INNER JOIN hits on users.id = hits.user_id

Your result would come through as a flat array but you can easily loop through and format your array accordingly.

您的结果将作为一个平面数组通过，但是您可以轻松地循环并相应地格式化您的数组。

#3

the sql statement you need is simply using INNER JOIN (or LEFT JOIN if ou want to return records even a certain user has no matching record on table hits)

您需要的sql语句只是使用内部连接(或者如果您想返回记录，则使用左连接，即使某个用户在表命中没有匹配记录)

SELECT  a.*, b.time
FROM    users a
        INNER JOIN hits b
            ON a.userid = b.userid

the result of the query is a single dimensional array so you need to format the result in the application level.

查询的结果是一个单维数组，因此需要在应用程序层中格式化结果。

To further gain more knowledge about joins, kindly visit the link below:

为了进一步了解加入的更多信息，请访问下面的链接:

Visual Representation of SQL Joins
SQL连接的可视化表示。

#4

SELECT * FROM users
LEFT JOIN hits on users.id = hits.user_id

#1