mysql - 结合MySQL查询帮助——2张表

Question

我有一个表rosters 和一个表members。它们是这样设置的:

rosters table
id     team     member
 1     1        1
 2     1        2
 3     1        3

members table
id     name     last_active
 1     Dan      1454815000
 2     Ewan     1454817500
 3     Cate     1454818369 

我需要获取 rosters 中 team=1 的所有行。然后，我需要获取所有返回的结果并使用 member 列从 members 中获取最大的 last_active 值，其中 id 在 rosters

返回的结果列表中

这就是我在 PHP 中的做法，但我确信有一种方法可以使用更高效的查询。

$rosterList = $db->query('SELECT * FROM rosters WHERE team=1');
$lastActive = 0;
foreach($rosterList as $roster) {
    $activity = $db->query('SELECT last_active FROM members WHERE id='.$roster['team']);
    if ( $activity > $lastActive )
        $lastActive = $activity;
}

if ( $lastActive > time()-60 )
    echo 'team is currently online';

如果它只返回一个包含最新的 last_active 列的结果就好了，但如果它返回 members 表中的所有匹配项，那也很好。

Answer 1

通过在 last_actie 列上使用 ORDER BY(降序)，然后限制为仅 1 行，您可以访问整个成员行。

sqlfiddle demo

MySQL 5.6 架构设置:

CREATE TABLE members
    (`id` int, `name` varchar(4), `last_active` int)
;

INSERT INTO members
    (`id`, `name`, `last_active`)
VALUES
    (1, 'Dan', 1454815000),
    (2, 'Ewan', 1454817500),
    (3, 'Cate', 1454818369)
;

CREATE TABLE rosters
    (`id` int, `team` int, `member` int)
;

INSERT INTO rosters
    (`id`, `team`, `member`)
VALUES
    (1, 1, 1),
    (2, 1, 2),
    (3, 1, 3)
;

查询 1:

select
    m.*
from members m
join rosters r on m.id = r.member
where r.team = 1
order by m.last_active DESC
limit 1

Results :

| id | name | last_active |
|----|------|-------------|
|  3 | Cate |  1454818369 |