sql分组数据去重
#分组获得每个机柜里服务器占用的机架总数，如552807e6-b428-4184-b219-ae368c68ddb3占用4个
mysql> select cabinet_uuid, count(host_uuid) from rack where not host_uuid is NULL group by cabinet_uuid;
+--------------------------------------+------------------+
| cabinet_uuid | count(host_uuid) |
+--------------------------------------+------------------+
| 552807e6-b428-4184-b219-ae368c68ddb3 | 4 |
| 55ce78c0-1c2c-4d34-b383-4028fe45a2fa | 7 |
| 6662c7d9-1cd7-426b-8616-5190c434bc1e | 21 |
| 6b293326-00d0-4d59-9fd9-5c18273fcbcb | 2 |
| 77b86411-93d8-4af8-afd1-08ee5f90435d | 11 |
| 8f233310-cf75-457b-b7c4-4476e48cfab2 | 8 |
| 92da441c-54bf-4e9f-8b96-4e53cdb6dee4 | 19 |
| a3b6ebdf-7c6b-4571-b079-e7bcef662c4e | 14 |
| a7d76dd6-bbf9-4cf8-a1f9-40697a95f03f | 17 |
| e3b18a11-b542-4dbd-955f-f2d2f38e901b | 15 |
+--------------------------------------+------------------+
#机柜552807e6-b428-4184-b219-ae368c68ddb3里有2u服务器1台，1u服务器2台，也就是说2u服务器有2条一样的host_uuid,使用DISTINCT去重，得到每机柜准确的设备数
mysql> select cabinet_uuid, count(DISTINCT host_uuid) from rack where not host_uuid is NULL group by cabinet_uuid;
+--------------------------------------+---------------------------+
| cabinet_uuid | count(DISTINCT host_uuid) |
+--------------------------------------+---------------------------+
| 552807e6-b428-4184-b219-ae368c68ddb3 | 3 |
| 55ce78c0-1c2c-4d34-b383-4028fe45a2fa | 4 |
| 6662c7d9-1cd7-426b-8616-5190c434bc1e | 14 |
| 6b293326-00d0-4d59-9fd9-5c18273fcbcb | 2 |
| 77b86411-93d8-4af8-afd1-08ee5f90435d | 7 |
| 8f233310-cf75-457b-b7c4-4476e48cfab2 | 5 |
| 92da441c-54bf-4e9f-8b96-4e53cdb6dee4 | 12 |
| a3b6ebdf-7c6b-4571-b079-e7bcef662c4e | 9 |
| a7d76dd6-bbf9-4cf8-a1f9-40697a95f03f | 13 |
| e3b18a11-b542-4dbd-955f-f2d2f38e901b | 11 |
+--------------------------------------+---------------------------+
posted on
2017-01-19 12:32
北京涛子 阅读(
...) 评论(
)
编辑
收藏
转载于:https://www.cnblogs.com/liujitao79/p/6306121.html
