另一列中每个值的distinct of列计数
另一列中每个值的distinct of列计数
提问于 2018-06-05 07:30:19
我在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 |
+-------------+-----------------+---------------+-----+我怎样才能做到这一点呢？
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-06-05 08:08:54
仅根据序列号将表连接到自身，以获得计数：
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这允许表的每一行都在结果行中，同时查找所有时间戳，这需要命中其他行。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50690089
复制相关文章
相似问题