mysql - 另一列中每个值的不同列计数

Question

我有一张像下面这样的表格MySQL。我想选择serial_num, devicetype,device_model和distinct of timestamp for each serial_num

+-------------+-----------------+---------------+------------------------+
| serial_num  |   devicetype    | device_model  |        timestamp       |
+-------------+-----------------+---------------+------------------------+
| 58172A0396  |                 |               | 2003-01-02 17:37:15.0  |
| 58172A0396  |                 |               | 2003-01-02 17:37:15.0  |
| 46C5Y00693  | Mac Pro         | Mac PC        | 2018-01-03 17:17:23.0  |
| 1737K7008F  | Windows PC      | Windows PC    | 2018-01-05 11:12:31.0  |
| 1737K7008F  | Network Device  | Unknown       | 2018-01-05 11:12:31.0  |
| 1737K7008F  | Network Device  | Unknown       | 2018-01-05 11:12:31.0  |
| 1737K7008F  | Network Device  |               | 2018-01-06 03:12:52.0  |
| 1737K7008F  | Windows PC      | Windows PC    | 2018-01-06 03:12:52.0  |
| 1737K7008F  | Network Device  | Unknown       | 2018-01-06 03:12:52.0  |
| 1665NF01F3  | Network Device  | Unknown       | 2018-01-07 03:42:34.0  |
+----------------+-----------------+---------------+---------------------+

我试过如下

select
    serial_num,
    devicetype,
    device_model,
    count(distinct timestamp)
from table
group by serialnum, devicetype, device_model

我想要的结果是

+-------------+-----------------+---------------+-----+
| serial_num  |   devicetype    | device_model  |count|
+-------------+-----------------+---------------+-----+
| 58172A0396  |                 |               |  1  |
| 58172A0396  |                 |               |  1  |
| 46C5Y00693  | Mac Pro         | Mac PC        |  1  |
| 1737K7008F  | Windows PC      | Windows PC    |  2  |
| 1737K7008F  | Network Device  | Unknown       |  2  |
| 1737K7008F  | Network Device  | Unknown       |  2  |
| 1737K7008F  | Network Device  |               |  2  |
| 1737K7008F  | Windows PC      | Windows PC    |  2  |
| 1737K7008F  | Network Device  | Unknown       |  2  |
| 1665NF01F3  | Network Device  | Unknown       |  1  |
+-------------+-----------------+---------------+-----+

我怎样才能做到这一点？

标签： mysqlgroup-bycount

Answer 1

仅在序列号上将表连接到自身以获取计数：

select
    t1.serial_num,
    t1.devicetype,
    t1.device_model,
    count(distinct t2.timestamp)
from table t1
join table t2 on t1.serial_num = t2.serial_num
group by 1, 2, 3

这允许表的每一行都在结果行中，同时查找所有时间戳，这需要点击其他行。