另一列中每个值的distinct of列计数

8ehkhllq 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(409)

我有一张像下面这样的table 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 |
+-------------+-----------------+---------------+-----+

我怎样才能做到这一点？

mysql group-by count

来源：https://stackoverflow.com/questions/50690089/count-of-distinct-of-column-for-each-value-in-another-column

1条答案

按热度按时间

rnmwe5a21#

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

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

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

赞(0）回复(0）举报 2021-06-20

我来回答

另一列中每个值的distinct of列计数

1条答案

相关问题

热门标签

最新问答