php - 结合来自两个不同表的 SQL 值并对其使用算术

Question

1. 我有一个包含事件嘉宾数据的表格。我可以通过以下查询检索已出席的人数 (membersAttended):

   SELECT COUNT(DISTINCT g.`name1`) AS `membersAttended`
       FROM `tmc_doorapp_guests` g
       LEFT JOIN `tmc_doorapp_events` e
       ON e.`id` = g.`event_id`
       WHERE `name1` REGEXP '^[0-9]+$'
       AND e.`date` > DATE_SUB(NOW(), INTERVAL 30 DAY)
       AND g.`checkin` = 1;
   

2. 要检索成员总数 (totalMembers)，我使用:

   SELECT COUNT(DISTINCT `id`) AS `totalMembers`
       FROM `tmc_users`;
   

3. 为了获得参加过所有成员的成员的百分比值 (totalPercent)，我使用 PHP 进行两个查询，然后计算如下所以:

   $totalPercent = number_format(($membersAttended/$totalMembers*100),2);
   

---

我试过以类似于下面的方式组合这两个查询和百分比计算，但没有成功:

    SELECT COUNT(DISTINCT g.`name1`) AS `membersAttended`,
        COUNT(DISTINCT m.`id`) AS `totalMembers`,
        (`membersAttended` * 100 / `totalMembers`) AS `totalPercent`
        FROM `tmc_doorapp_guests` g, `tmc_users` m
        LEFT JOIN `tmc_doorapp_events` e
        ON e.`id` = g.`event_id`
        WHERE `name1` REGEXP '^[0-9]+$'
        AND e.`date` > DATE_SUB(NOW(), INTERVAL 30 DAY)
        AND g.`checkin` = 1;

我还尝试扩展列:

    SELECT COUNT(DISTINCT g.`name1`) AS `membersAttended`,
        COUNT(DISTINCT m.`id`) AS `totalMembers`,
        (COUNT(DISTINCT g.`name1`) * 100 / COUNT(DISTINCT m.`id`)) AS `totalPercent`
        FROM `tmc_doorapp_guests` g, `tmc_users` m
        LEFT JOIN `tmc_doorapp_events` e
        ON e.`id` = g.`event_id`
        WHERE `name1` REGEXP '^[0-9]+$'
        AND e.`date` > DATE_SUB(NOW(), INTERVAL 30 DAY)
        AND g.`checkin` = 1;

这给了我错误:

Error code: 1054. Unknown column 'g.event_id' in 'on clause'

如何将这些 SQL/PHP 查询/计算合并到一个 SQL 查询中？

Answer 1

你可以使用子查询:

SELECT COUNT(DISTINCT g.`name1`) AS `membersAttended`,
       (SELECT COUNT(DISTINCT `id`) FROM `tmc_users`) AS `totalMembers`,
       COUNT(DISTINCT g.`name1`) * 100 / (SELECT COUNT(DISTINCT `id`)
                                          FROM `tmc_users`) AS `totalPercent`
FROM `tmc_doorapp_guests` g
LEFT JOIN `tmc_doorapp_events` e
ON e.`id` = g.`event_id`
WHERE `name1` REGEXP '^[0-9]+$'
AND e.`date` > DATE_SUB(NOW(), INTERVAL 30 DAY)
AND g.`checkin` = 1;