php - 合并 2 个 SQL 表中条目的总数

Question

我似乎找不到正确的方法，所以我希望有人能给我一些指导？

SQL 数据库的结构如下(我删除了不相关的内容):

Requests
R_ID    R_FulfilledBy
1       Bob
2       Craig
3       Bob

SIMs
SM_ID   SM_FulfilledBy
1       Bob
2       Craig
3       Bob

我希望最终得到这样的输出:

Fulfilled By    Requests
Bob         4
Craig       2

这是我的 PHP/HTML:

<div id="table">
        <?php

            //Connect to MySQL Database
            $connection = mysql_connect($runnerdbServer, $runnerdbUser, $runnerdbPass);
            mysql_select_db($runnerdbName, $connection) or die("MysQL Error");

                            $query = "SELECT R_FulfilledBy, COUNT(R_ID) FROM Requests GROUP BY R_FulfilledBy ORDER BY COUNT(R_ID) DESC"; 


                            $result = mysql_query($query) or die(mysql_error());



        ?>

    <!-- Number of Runners (Counts total number of records in Requests table) -->
    <table border='0' width='50%'>
        <tr>
        <th>Runners Fulfilled</th>
        <tr><td><?php
            $query = mysql_query("SELECT * FROM Requests");
            $number=mysql_num_rows($query);
            echo $number; 
            ?>
        </td></tr>
    </table>
    &nbsp;

    <!-- Fulfillment Stats -->
    <table border='0' width='50%'>
        <tr>
        <th>Name</th>
        <th>Runners Fulfilled</th>
        </tr>

    <?
    // Print out result (I want this to calculate requests fulfilled by each user in 'Requests' and 'SIMs' table)
    while($row = mysql_fetch_array($result)){
    echo "<tr>";
    echo "<td>". $row['R_FulfilledBy'] ."</td>";
    echo "<td>". $row['COUNT(R_ID)'] ."</td>";
    echo "</tr>";
    }

    ?>

    </table>

目前它只计算'Requests'表中的记录:(

Answer 1

您可以在子查询中联合所有两个表:

select  FulfilledBy
,       count(*)
from    (
        select  R_FulfilledBy as FulfilledBy
        from    Requests
        union all
        select  SM_FulfilledBy
        from    SIMs
        ) as SubQueryAlias
group by
        FulfilledBy

使用 union all 而不是 union 因为后者消除了重复；这将使每个人的最大计数为 1。