mysql - 表数据计数最大值

Question

我在 MySQL 中有两个表

+---------+-----------+
| machine | status    |
+---------+-----------+
| 40001   | Completed | 
| 40001   | Completed | 
| 40001   | Completed | 
| 40001   | Completed | 
| 40001   | Pending   | 
| 40001   | Pending   | 
| 40001   | Pending   | 
| 40001   | Pending   | 
| 40001   | Pending   | 
| 40001   | Pending   | 
+---------+-----------+
And the other one as
+---------+---------+
| machine | packets | 
+---------+---------+
| 40001   |   527   |
| 40001   |   1497  |
| 40002   |   1414  | 
| 40002   |   2796  |
| 40003   |  392    | 
| 40003   |  1663   | 
| 40004   |   500   | 
| 40004   |   1277  |
+-------+----------+

我想编写一个选择查询，为我提供该机器的机器、完成计数、挂起计数和最大数据包数。所以我尝试了

SELECT machine,max(packets) AS sync,
      sum(if(laststatus='completed', 1, 0)) AS generation,
      sum(if(laststatus != 'completed', 1, 0)) AS pending
FROM machine_status
right join machine_packets on machine_packets.machine=machine_status.machine
GROUP BY machine

但是我得到了:

+---------+------+------------+---------+
| machine | sync | generation | pending |
+---------+------+------------+---------+
| 40001   | 1497 |          8 |       2 | 
| 40002   | 2796 |          4 |       2 | 
| 40003   | 1663 |          6 |       0 | 
| 40004   | 1277 |          0 |       2 | 
| 40005   | 2755 |          0 |       0 | 
| 40006   |  927 |          0 |       0 | 
| 40007   |  306 |          0 |       0 | 
+---------+------+------------+---------+

正如我们所看到的，生成列和挂起列中的值加倍。我哪里出错了？

Answer 1

SELECT machine,sync,
      sum(if(laststatus='completed', 1, 0)) AS generation,
      sum(if(laststatus != 'completed', 1, 0)) AS pending
FROM machine_status
right join (select machine, 
                   max(packets) AS sync 
            from machine_packets 
            group by machine) mp on mp.machine=machine_status.machine
GROUP BY machine

它们加倍，因为 machine_packets 每个 id 有 2 条记录。为了避免这种情况，您可以将其移动到子查询中