代码之家 › 专栏 › 技术社区 › user756659

mysql pdo-用join分组的表的最新行

greatest-n-per-group sql-order-by group-by pdo mysql

user756659 · 技术社区 · 5 年前

我为标题描述道歉,因为我不知道如何最好地描述这个。

有多台计算机,每台计算机有多个用户,每个用户有多个数据日志。我想获取每台计算机的最新数据日志。

我现在认识到,下面的查询没有提供正确的数据,因为ORDERBY发生在结果中,而GROUPBY没有以特定的顺序发生。

我在发布这个之前已经阅读过了,它似乎需要一个子查询,然后在同一个表上再次联接该查询的结果,以获得该行的其余列值(被发现是最新的)。不过,我发现的示例非常基本,不涉及正在联接的其他表。似乎我也要这样做两次,因为我希望它按计算机分组…(对于按用户ID分组的数据日志,一次获取最大值,然后另一次按计算机ID分组以获取早期结果的最大值),或者可能我错了。

需要一些帮助来掌握如何解决这一问题以及解决这一问题的方法。

$stmt = $db->prepare("
    SELECT
        computers.computer_name,
        users.username,
        data_logs.window_title,
        data_logs.filename,
        data_logs.capture_timestamp
    FROM computers
    INNER JOIN users
        ON users.computer_id = computers.computer_id
    INNER JOIN data_logs
        ON data_logs.user_id = users.user_id AND data_logs.marked != 1
    WHERE computers.account_id = :cw_account_id AND computers.status = 1
    GROUP BY computers.computer_id
    ORDER BY data_logs.capture_timestamp desc
");

$binding = array('cw_account_id' => 1721);
$stmt->execute($binding);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo "<pre>";
print_r($results);
echo "</pre>";

1 回复 | 直到 5 年前

GMB 5 年前

一种解决方案是使用相关的子查询。原则是外部查询不聚合,但它在 WHERE 使用聚合子查询为当前计算机ID选择最新日志项的子句。

我想您需要这个查询(如果看不到样本数据和预期的输出,这可能不是100%准确的):

SELECT
    computers.computer_name,
    users.username,
    data_logs.window_title,
    data_logs.filename,
    data_logs.capture_timestamp
FROM computers
INNER JOIN users
    ON users.computer_id = computers.computer_id
INNER JOIN data_logs
    ON data_logs.user_id = users.user_id AND data_logs.marked != 1
WHERE 
    computers.account_id = :cw_account_id 
    AND computers.status = 1
    AND data_logs.capture_timestamp = (
        SELECT MAX(d.capture_timestamp)
        FROM computers c
                INNER JOIN users u ON u.computer_id = c.computer_id
        INNER JOIN data_logs d ON d.user_id = u.user_id AND d.marked != 1
        WHERE c.computer_id = computers.computer_id AND c.account_id = computers.account_id
    )
ORDER BY data_logs.capture_timestamp desc