我有一个保存Mysql中设备及其关系的表。
一个设备可以关联多个对象。我想要唯一的deviceId,这样我就不会多次计算设备,并提供所有设备的总容量。
Array | Capacity | deviceId | Assignment
4321 1024 3eb esx1
4321 1024 3eb esx2
4321 1024 3eb esx3
4321 28672 3ec win1
4321 61440 9f1 unassigned
我想要一个类似于下面的报告
Array | Capacity
4321 91136
我试过select sum(Capacity) from table group by deviceId
,这并没有给我预期的结果。
假设重复数据(前3列),如Question:
create table table1
( id int auto_increment primary key,
Array int not null,
Capacity int not null,
deviceId varchar(100) not null,
Assignment varchar(100) not null
);
truncate table table1;
insert table1(array,capacity,deviceId,assignment) values
(4321,1024,'3eb','blah'),
(4321,1024,'3eb','blah'),
(4321,1024,'3eb','blah'),
(4321,28672,'3ec','blah'),
(4321,61440,'9f1','blah');
select Array,sum(Capacity) as Capacity
from
( select distinct Array,Capacity,deviceId
from table1
) xxx
group by Array;
+-------+----------+
| Array | Capacity |
+-------+----------+
| 4321 | 91136 |
+-------+----------+