php - 将 SELECT 记录与 COUNT 结合起来

Question

我这里有一个 MySQL 查询，效果很好!不过，我还需要做一件额外的事情。目前，它的返还比例最高。最高百分比基于特定日期在数据库中生成特定记录类型的出现次数。我想要它做的是返回具有如此高百分比的关联记录的名称。我尝试将其合并到下面的查询中，但似乎总是失败。对此的任何建议都会很棒。

SELECT *, COALESCE((acknowledged / generated), 0) AS result
FROM    (
    SELECT  (
        SELECT      COUNT(*)
        FROM        logs
        INNER JOIN  generated_logs
        ON          generated_logs.log_fk = logs.log_pk 
        WHERE       logs.department_fk = ?
        AND         DATE(generated_logs.generated_time) = ?
    ) AS generated, (
    SELECT      COUNT(generated_logs.log_fk) AS magnitude 
    FROM        logs
    INNER JOIN  generated_logs
    ON          generated_logs.log_fk = logs.log_pk
    WHERE       logs.department_fk = ?
    AND         generated_logs.acknowledged = 1
    AND         DATE(generated_logs.generated_time) = ?
    GROUP BY    generated_logs.log_fk
    ORDER BY    magnitude DESC
    LIMIT 1         
    ) AS acknowledged ) AS result

当前输出是:

+-----------+--------------+--------+
| Generated | Acknowledged | Result |
+-----------+--------------+--------+
|     6     |       3      |  0.50  |
+-----------+--------------+--------+

我需要的是:

+-----------+--------------+--------+----------+
| Generated | Acknowledged | Result |   Name   |
+-----------+--------------+--------+----------+
|     6     |       3      |  0.50  | Test Log |
+-----------+--------------+--------+----------+

名称是日志表中的一个字段。

Answer 1

再试一次...

SELECT *, COALESCE((a.magnitude / g.generated), 0) AS result
   FROM
       (
           SELECT      COUNT(*) AS generated
           FROM        logs
           INNER JOIN  generated_logs
           ON          generated_logs.log_fk = logs.log_pk 
           WHERE       logs.department_fk = ?
           AND         DATE(generated_logs.generated_time) = ?
       ) AS g,
       (
            SELECT COUNT(generated_logs.log_fk) AS magnitude, logs.name
            FROM        logs
            INNER JOIN  generated_logs
            ON          generated_logs.log_fk = logs.log_pk
            WHERE       logs.department_fk = ?
            AND         generated_logs.acknowledged = 1
            AND         DATE(generated_logs.generated_time) = ?
            GROUP BY    generated_logs.log_fk
            ORDER BY    magnitude DESC
            LIMIT 1 
       ) AS a