php - 连接 MySQL 表和计算计数 - PHP 输出

Question

我正在尝试查询一个繁忙的公共(public)计算机实验室中可用计算机的 MySQL 数据库。我有两张 table ，COMPUTERS 和 COMPUSE。每次用户登录或注销计算机时都会更新 COMPUSE 表。当注销时间为空时，计算机正在使用中。在此示例中，计算机 1、2、3 和 8 正在使用中。计算机 4、5、6、7、9、10 可用。

|-----------------------|-----------------------|----------|-----------|
|       logontime       |       logofftime      |   recID  | compname  |
|-----------------------|-----------------------|----------|-----------|
|  2011-05-13 13:45:16  |             <<null>>  |  310052  |  Comp001  |
|  2011-05-13 13:35:18  |  2011-05-13 13:39:37  |  310043  |  Comp001  |
|  2011-05-13 12:12:09  |  2011-05-13 12:33:37  |  309979  |  Comp001  |
|  2011-05-13 13:00:57  |             <<null>>  |  310018  |  Comp002  |
|  2011-05-13 11:30:13  |  2011-05-13 12:58:15  |  309940  |  Comp002  |
|  2011-05-13 09:36:15  |  2011-05-13 09:47:22  |  309850  |  Comp002  |
|  2011-05-13 09:25:29  |             <<null>>  |  309840  |  Comp003  |
|  2011-05-13 08:45:38  |  2011-05-13 09:24:03  |  309793  |  Comp003  |
|  2011-05-12 22:39:58  |  2011-05-13 00:36:31  |  309640  |  Comp003  |
|  2011-05-13 12:06:22  |  2011-05-13 12:50:23  |  309972  |  Comp004  |
|  2011-05-13 11:10:16  |  2011-05-13 12:01:16  |  309915  |  Comp004  |
|  2011-05-13 07:17:18  |  2011-05-13 09:42:10  |  309731  |  Comp004  |
|  2011-05-13 11:51:38  |  2011-05-13 12:15:35  |  309959  |  Comp005  |
|  2011-05-13 08:55:14  |  2011-05-13 09:47:48  |  309807  |  Comp005  |
|  2011-05-12 18:15:05  |  2011-05-12 18:15:16  |  309502  |  Comp005  |
|  2011-05-13 12:08:40  |  2011-05-13 13:16:41  |  309974  |  Comp006  |
|  2011-05-13 11:29:09  |  2011-05-13 12:05:56  |  309939  |  Comp006  |
|  2011-05-13 11:10:41  |  2011-05-13 11:19:14  |  309916  |  Comp006  |
|  2011-05-13 10:45:27  |  2011-05-13 11:16:44  |  309896  |  Comp007  |
|  2011-05-13 09:21:42  |  2011-05-13 09:55:48  |  309839  |  Comp007  |
|  2011-05-13 08:23:33  |  2011-05-13 09:14:24  |  309770  |  Comp007  |
|  2011-05-13 13:54:12  |             <<null>>  |  310058  |  Comp008  |
|  2011-05-13 13:38:53  |  2011-05-13 13:39:23  |  310045  |  Comp008  |
|  2011-05-13 10:13:23  |  2011-05-13 13:26:51  |  309878  |  Comp008  |
|  2011-05-13 12:16:06  |  2011-05-13 13:26:21  |  309984  |  Comp009  |
|  2011-05-13 10:13:09  |  2011-05-13 12:15:13  |  309877  |  Comp009  |
|  2011-05-13 08:23:22  |  2011-05-13 10:07:08  |  309769  |  Comp009  |
|  2011-05-13 13:45:51  |  2011-05-13 13:47:11  |  310053  |  Comp010  |
|  2011-05-13 11:18:12  |  2011-05-13 13:19:59  |  309925  |  Comp010  |
|  2011-05-13 07:28:50  |  2011-05-13 09:50:09  |  309737  |  Comp010  |
|-----------------------|-----------------------|----------|-----------|

此数据需要与一个表格连接，该表格指示计算机所在建筑物的哪一层。该表看起来与此类似:

|--------|-----------|-------------|
| compID | compname  |   LOCATION  |
|--------|-----------|-------------|
|    95  |  Comp001  |  1st Floor  |
|    96  |  Comp002  |  1st Floor  |
|    97  |  Comp003  |  1st Floor  |
|    98  |  Comp004  |  1st Floor  |
|    99  |  Comp005  |  2nd Floor  |
|   100  |  Comp006  |  2nd Floor  |
|   101  |  Comp007  |  2nd Floor  |
|   102  |  Comp008  |  3rd Floor  |
|   103  |  Comp009  |  3rd Floor  |
|   104  |  Comp010  |  3rd Floor  |
|--------|-----------|-------------|

第一个表 COMPUSE 中有数千条记录，用于计算实验室的使用情况统计信息。我需要创建每个级别上可用的计算机数量的输出。我不知道如何在不中断查询的情况下将 COMPUTERS 表中的位置连接到 COMPUSE 表中的 compname。最初，我运行了以下查询以确定可用计算机的总数，但我确实需要能够按建筑物的面积进行分割。

SELECT
    (SELECT COUNT(compname) 
        FROM compusage.computers) - 
    (SELECT COUNT(compname) 
        FROM compusage.compuse 
        WHERE logofftime IS NULL)

任何人都可以帮我构造一个查询来输出建筑物每一层可用计算机的数量吗？此示例中的预期结果为:

Level 1:  1 computer (of 4) available
Level 2:  3 computers (of 3) available
Level 3:  2 computers (of 3) available

谢谢，约旦

更新:这与我正在尝试做的非常相似，但我也不知道如何调整这些查询:PHP::Group and subtract two tables

更新 2:这是我现在正在尝试调整的内容，但我对子查询的理解还不够好，无法使其正常工作:

select (totalcomps.total - inuse.inusecomps) as available, totalcomps.total 
from (SELECT count(compname) as total, location 
         FROM compuse.computers
         GROUP BY location) as totalcomps 
inner join (SELECT count(compname) as inusecomps 
             FROM computers.compuse
             WHERE logofftime is null
             GROUP BY location) as inuse 
on computers.compname = compuse.compname

更新 3:我更新了示例数据，使其更像是一个包含更多记录的真实示例。

Answer 1

你试过吗？

SELECT LOCATION, COUNT(1) as numberOfComps 
FROM COMPUTERS c
   LEFT JOIN COMPUSE cu ON c.COMPNAME = cu.Computer 
WHERE logofftime IS NULL 
GROUP BY LOCATION