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

1. I have a table with data of guests to an event. I can retrieve the number of people who have attended (membersAttended) with this query:

   我有一张桌子上有一个活动的客人数据。我可以使用此查询检索有人参加过的人数（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. To retrieve the total number of members (totalMembers) I use:

   要检索我使用的成员总数（totalMembers）：

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

3. To get a percentage value (totalPercent) of members who have attended across the total members, I use PHP to make the two queries and then calculate like so:

   为了获得参与总成员的成员的百分比值（totalPercent），我使用PHP来进行两个查询，然后计算如下：

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

---

I've tried combining the two queries and the percentage calculation in a similar way to below, with no luck:

我尝试以下面类似的方式将两个查询和百分比计算结合起来，没有运气：

    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;

I also tried expanding out the columns:

我也尝试扩展列：

    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;

This gives me the error:

这给了我错误：

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

错误代码：1054。'on子句'中的未知列'g.event_id'

How can I combine these SQL/PHP queries/calculations into one SQL query?

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

1 个解决方案

#1

You could use subquery:

您可以使用子查询：

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;