mysql - 使用各种条件和表格获取/优化计数

Question

我试图在一次调用中返回不同条件和表格的计数。我之前将其中每一个都放在自己的子查询中，但意识到查询 realtime_logs 表 4 次是没有意义的......所以我将其更改为下面的 stmt，它按预期工作。

$stmt = $db->prepare("
    SELECT
        sum(case when event_title = 'opened' then 1 end) as opened, 
        sum(case when event_title = 'closed' then 1 end) as closed,
        sum(case when event_title = 'viewed' then 1 end) as viewed,
        sum(case when event_title LIKE '%blocked%' then 1 end) as blocked 
    FROM realtime_logs
");
$stmt->execute();

$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

这样做虽然我失去了从其他表获取计数的能力...我想要这样的东西...

$stmt = $db->prepare("
        SELECT
            sum(case when event_title = 'opened' then 1 end) as opened, 
            sum(case when event_title = 'closed' then 1 end) as closed,
            sum(case when event_title = 'viewed' then 1 end) as viewed,
            sum(case when event_title LIKE '%blocked%' then 1 end) as blocked 
        FROM realtime_logs

       // I also want to return the count for this different table

       (SELECT COUNT(location_id)
        FROM spc_location_logs
       ) as locations
");

Answer 1

为什么不能简单地分成两个语句/查询？您可以使用 PHP after 将返回值合并到一个数组中。试试这个:

$stmt1 = $db->prepare("
    SELECT
        sum(case when event_title = 'opened' then 1 end) as opened, 
        sum(case when event_title = 'closed' then 1 end) as closed,
        sum(case when event_title = 'viewed' then 1 end) as viewed,
        sum(case when event_title LIKE '%blocked%' then 1 end) as blocked,
    FROM realtime_logs
");

$stmt2 = $db->prepare('
    SELECT COUNT(location_id) AS locations
    FROM spc_location_logs
');

$stmt1->execute();
$stmt2->execute();

$arr1 = $stmt1->fetchAll(PDO::FETCH_KEY_PAIR);
$arr2 = $stmt2->fetchAll(PDO::FETCH_KEY_PAIR);

$arr  = array_merge($arr1, $arr2);

请注意，我使用 FETCH_KEY_PAIR 模式。