机柜变更单执行表
CREATE TABLE `chanage_cabinets_execute` (
`ID` int(50) NOT NULL auto_increment,
`APPLICATION_ID` varchar(200) default NULL,
`ATTITUDE` varchar(200) default NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='机柜执行单' AUTO_INCREMENT=35 ;变更单表：机柜变更单是一种变更单
如果以chanage_apply为主，需限定CHANAGE_TYPE=‘CABI、’，代表变更类型为机柜变更
CREATE TABLE `chanage_apply` (
`ID` int(11) NOT NULL auto_increment,
`CHANAGE_TYPE` varchar(50) ，
`OPERATION_ID` int(11) default NULL,
`PRODUCT_ID` int(11) default NULL,
`MRL` int(10) default NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='变更单' AUTO_INCREMENT=162 ;机柜变更表，是一种变更单
CREATE TABLE `chanage_cabinets` (
`ID` int(11) NOT NULL auto_increment,
`APPLICATION_ID` int(11) NOT NULL,
`TYPE` varchar(50) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='机柜变更'
AUTO_INCREMENT=44 ;说明：
APPLICATION_ID 引用自chanage_apply表的IDOPERATION_ID 所属业务PRODUCT_ID 产品IDMRL 所在机房ATTITUDE 查询条件时限定为'Y'TYPE 机柜变更的类型,有这几个值：ONSHOW 上架，WITHDRAW 下架，MOVE 迁移现需要统计，各个机房里面，各业务的各个产品的机柜上架数量、下架数量、迁移数量
也就是首先按机房进行分组，分组后再按业务分组，再按产品分组
上架数量:CHANAGE_TYPE为ONSHOW时的总计
下架数量：CHANAGE_TYPE为WITHDRAW时的总计
迁移数量：CHANAGE_TYPE为MOVE时的总计统计结果显示如下：
所在机房 所属业务 产品 上架数 下架数 迁移数
1 2 1 3 4 2
1 2 2 5 5 5
2 2 1 4 5 6
2 2 2 2 5 7
3 1 5 9 10 12关联关系说明：
一个业务有多个产品，一个产品只属于一个业务，每个业务可能对各个机房作了机柜变更，因为每个业务可能在每个机房都有机柜，机柜上放置的是各个业务的服务器。各个业务不定期的会在各机房作上架、下架、迁移操纵，这里上架下架迁移指的是服务器上架到机柜、从机柜下架、从一个机柜迁移到另一个机柜
sum(if(TYPE='ONSHOW',1,0) as 上架数,
sum(if(TYPE='WITHDRAW',1,0) as 下架数,
sum(if(TYPE='MOVE',1,0) as 迁移数
from chanage_apply c,chanage_cabinets d
where c. -- ??????? 看不出你这两个表如何联接。
group by c.MRL,c.OPERATION_ID,c.PRODUCT_ID
这里要以另外两张表为主，连接chanage_apply，就OK，查到的就都是机柜变更的单子关联关系如下：
chanage_apply.id=chanage_cabinets.APPLICATION_ID
chanage_apply.id=chanage_cabinets_execute.APPLICATION_ID
测试数据:INSERT INTO `chanage_apply`(id,OPERATION_ID,PRODUCT_ID,MRL) VALUES (438, 4 , 84 , 7);
INSERT INTO `chanage_apply`(id,OPERATION_ID,PRODUCT_ID,MRL) VALUES (437, 4 , 84 , 7);
INSERT INTO `chanage_apply`(id,OPERATION_ID,PRODUCT_ID,MRL) VALUES (436, 4 , 84 , 7);
INSERT INTO `chanage_apply`(id,OPERATION_ID,PRODUCT_ID,MRL) VALUES (455, 2 , 2 , 7);
INSERT INTO `chanage_apply`(id,OPERATION_ID,PRODUCT_ID,MRL) VALUES (454, 2 , 2 , 7);
INSERT INTO `chanage_apply`(id,OPERATION_ID,PRODUCT_ID,MRL) VALUES (453, 2 , 2 , 7);
INSERT INTO `chanage_apply`(id,OPERATION_ID,PRODUCT_ID,MRL) VALUES (211, 80 , 135 , 4);
INSERT INTO `chanage_apply`(id,OPERATION_ID,PRODUCT_ID,MRL) VALUES (214, 80 , 135 , 4);
INSERT INTO `chanage_apply`(id,OPERATION_ID,PRODUCT_ID,MRL) VALUES (219, 80 , 135 , 4);
INSERT INTO `chanage_apply`(id,OPERATION_ID,PRODUCT_ID,MRL) VALUES (221, 80 , 135 , 4);
INSERT INTO `chanage_apply`(id,OPERATION_ID,PRODUCT_ID,MRL) VALUES (103, 8 , 96 , 7);
INSERT INTO `chanage_apply`(id,OPERATION_ID,PRODUCT_ID,MRL) VALUES (104, 8 , 96 , 7);
INSERT INTO `chanage_apply`(id,OPERATION_ID,PRODUCT_ID,MRL) VALUES (106, 77 , 23 , 7);
INSERT INTO `chanage_apply`(id,OPERATION_ID,PRODUCT_ID,MRL) VALUES (122, 2 , 2 , 7);
INSERT INTO `chanage_apply`(id,OPERATION_ID,PRODUCT_ID,MRL) VALUES (75, 64 , 6 , 18);
INSERT INTO `chanage_apply`(id,OPERATION_ID,PRODUCT_ID,MRL) VALUES (107, 47 , 114 , 18);
INSERT INTO `chanage_apply`(id,OPERATION_ID,PRODUCT_ID,MRL) VALUES (108, 47 , 114 , 18);
INSERT INTO `chanage_apply`(id,OPERATION_ID,PRODUCT_ID,MRL) VALUES (119, 80 , 119 , 18);INSERT INTO `chanage_cabinets`(id,APPLICATION_ID,TYPE) VALUES (168, 438 , 'WITHDRAW');
INSERT INTO `chanage_cabinets`(id,APPLICATION_ID,TYPE) VALUES (184, 437 , 'WITHDRAW');
INSERT INTO `chanage_cabinets`(id,APPLICATION_ID,TYPE) VALUES (183, 436 , 'WITHDRAW');
INSERT INTO `chanage_cabinets`(id,APPLICATION_ID,TYPE) VALUES (199, 455 , 'ONSHOW');
INSERT INTO `chanage_cabinets`(id,APPLICATION_ID,TYPE) VALUES (198, 454 , 'ONSHOW');
INSERT INTO `chanage_cabinets`(id,APPLICATION_ID,TYPE) VALUES (197, 453 , 'WITHDRAW');
INSERT INTO `chanage_cabinets`(id,APPLICATION_ID,TYPE) VALUES (73, 211 , 'WITHDRAW');
INSERT INTO `chanage_cabinets`(id,APPLICATION_ID,TYPE) VALUES (76, 214 , 'WITHDRAW');
INSERT INTO `chanage_cabinets`(id,APPLICATION_ID,TYPE) VALUES (81, 219 , 'WITHDRAW');
INSERT INTO `chanage_cabinets`(id,APPLICATION_ID,TYPE) VALUES (83, 221 , 'WITHDRAW');
INSERT INTO `chanage_cabinets`(id,APPLICATION_ID,TYPE) VALUES (28, 103 , 'ONSHOW');
INSERT INTO `chanage_cabinets`(id,APPLICATION_ID,TYPE) VALUES (29, 104 , 'WITHDRAW');
INSERT INTO `chanage_cabinets`(id,APPLICATION_ID,TYPE) VALUES (30, 106 , 'MOVE');
INSERT INTO `chanage_cabinets`(id,APPLICATION_ID,TYPE) VALUES (40, 122 , 'WITHDRAW');
INSERT INTO `chanage_cabinets`(id,APPLICATION_ID,TYPE) VALUES (26, 75 , 'ONSHOW');
INSERT INTO `chanage_cabinets`(id,APPLICATION_ID,TYPE) VALUES (37, 119 , 'WITHDRAW');
INSERT INTO `chanage_cabinets`(id,APPLICATION_ID,TYPE) VALUES (31, 107 , 'MOVE');
INSERT INTO `chanage_cabinets`(id,APPLICATION_ID,TYPE) VALUES (32, 108 , 'WITHDRAW');INSERT INTO `chanage_cabinets_execute`(id,APPLICATION_ID,ATTITUDE) VALUES (168, 438 , 'Y');
INSERT INTO `chanage_cabinets_execute`(id,APPLICATION_ID,ATTITUDE) VALUES (167, 437 , 'Y');
INSERT INTO `chanage_cabinets_execute`(id,APPLICATION_ID,ATTITUDE) VALUES (166, 436 , 'Y');
INSERT INTO `chanage_cabinets_execute`(id,APPLICATION_ID,ATTITUDE) VALUES (159, 455 , 'Y');
INSERT INTO `chanage_cabinets_execute`(id,APPLICATION_ID,ATTITUDE) VALUES (158, 454 , 'Y');
INSERT INTO `chanage_cabinets_execute`(id,APPLICATION_ID,ATTITUDE) VALUES (157, 453 , 'Y');
INSERT INTO `chanage_cabinets_execute`(id,APPLICATION_ID,ATTITUDE) VALUES (89, 211 , 'Y');
INSERT INTO `chanage_cabinets_execute`(id,APPLICATION_ID,ATTITUDE) VALUES (90, 214 , 'Y');
INSERT INTO `chanage_cabinets_execute`(id,APPLICATION_ID,ATTITUDE) VALUES (92, 219 , 'Y');
INSERT INTO `chanage_cabinets_execute`(id,APPLICATION_ID,ATTITUDE) VALUES (93, 221 , 'Y');
INSERT INTO `chanage_cabinets_execute`(id,APPLICATION_ID,ATTITUDE) VALUES (20, 103 , 'Y');
INSERT INTO `chanage_cabinets_execute`(id,APPLICATION_ID,ATTITUDE) VALUES (21, 104 , 'Y');
INSERT INTO `chanage_cabinets_execute`(id,APPLICATION_ID,ATTITUDE) VALUES (19, 106 , 'Y');
INSERT INTO `chanage_cabinets_execute`(id,APPLICATION_ID,ATTITUDE) VALUES (31, 122 , 'Y');
INSERT INTO `chanage_cabinets_execute`(id,APPLICATION_ID,ATTITUDE) VALUES (17, 75 , 'Y');
INSERT INTO `chanage_cabinets_execute`(id,APPLICATION_ID,ATTITUDE) VALUES (24, 107 , 'Y');
INSERT INTO `chanage_cabinets_execute`(id,APPLICATION_ID,ATTITUDE) VALUES (25, 108 , 'Y');
INSERT INTO `chanage_cabinets_execute`(id,APPLICATION_ID,ATTITUDE) VALUES (30, 119 , 'Y');
按机房MRL、所属业务OPERATION_ID、产品PRODUCT_ID 统计机柜变更上架数量、下架数量、迁移数量项目的需求是，
统计每个机房的服务器调配情况，即服务器的上架、下架、迁移数量（即机柜变更上架数量、下架数量、迁移数量）
机房相同时，按所属业务统计，业务相同的情况下，按产品进行统计，也就是按多个字段分组统计
得到的结果
机房 所属业务 产品 上架数量 下架数量 迁移数量
18 47 114 1 1
18 80 119 1
18 64 6 1
7 4 84 3
7 2 2 2 2
4 80 135 4
| 7 | 8 | 96 | 1 | 1 | 0 |
| 7 | 77 | 23 | 0 | 0 | 1 |
这两组数据？mysql> select * from chanage_cabinets_execute;
+-----+----------------+----------+
| ID | APPLICATION_ID | ATTITUDE |
+-----+----------------+----------+
| 17 | 75 | Y |
| 19 | 106 | Y |
| 20 | 103 | Y |
| 21 | 104 | Y |
| 24 | 107 | Y |
| 25 | 108 | Y |
| 30 | 119 | Y |
| 31 | 122 | Y |
| 89 | 211 | Y |
| 90 | 214 | Y |
| 92 | 219 | Y |
| 93 | 221 | Y |
| 157 | 453 | Y |
| 158 | 454 | Y |
| 159 | 455 | Y |
| 166 | 436 | Y |
| 167 | 437 | Y |
| 168 | 438 | Y |
+-----+----------------+----------+
18 rows in set (0.00 sec)mysql> select * from chanage_apply;
+-----+--------------+--------------+------------+------+
| ID | CHANAGE_TYPE | OPERATION_ID | PRODUCT_ID | MRL |
+-----+--------------+--------------+------------+------+
| 75 | NULL | 64 | 6 | 18 |
| 103 | NULL | 8 | 96 | 7 |
| 104 | NULL | 8 | 96 | 7 |
| 106 | NULL | 77 | 23 | 7 |
| 107 | NULL | 47 | 114 | 18 |
| 108 | NULL | 47 | 114 | 18 |
| 119 | NULL | 80 | 119 | 18 |
| 122 | NULL | 2 | 2 | 7 |
| 211 | NULL | 80 | 135 | 4 |
| 214 | NULL | 80 | 135 | 4 |
| 219 | NULL | 80 | 135 | 4 |
| 221 | NULL | 80 | 135 | 4 |
| 436 | NULL | 4 | 84 | 7 |
| 437 | NULL | 4 | 84 | 7 |
| 438 | NULL | 4 | 84 | 7 |
| 453 | NULL | 2 | 2 | 7 |
| 454 | NULL | 2 | 2 | 7 |
| 455 | NULL | 2 | 2 | 7 |
+-----+--------------+--------------+------------+------+
18 rows in set (0.00 sec)mysql> select * from chanage_cabinets;
+-----+----------------+----------+
| ID | APPLICATION_ID | TYPE |
+-----+----------------+----------+
| 26 | 75 | ONSHOW |
| 28 | 103 | ONSHOW |
| 29 | 104 | WITHDRAW |
| 30 | 106 | MOVE |
| 31 | 107 | MOVE |
| 32 | 108 | WITHDRAW |
| 37 | 119 | WITHDRAW |
| 40 | 122 | WITHDRAW |
| 73 | 211 | WITHDRAW |
| 76 | 214 | WITHDRAW |
| 81 | 219 | WITHDRAW |
| 83 | 221 | WITHDRAW |
| 168 | 438 | WITHDRAW |
| 183 | 436 | WITHDRAW |
| 184 | 437 | WITHDRAW |
| 197 | 453 | WITHDRAW |
| 198 | 454 | ONSHOW |
| 199 | 455 | ONSHOW |
+-----+----------------+----------+
18 rows in set (0.00 sec)mysql>
mysql> select c.MRL,c.OPERATION_ID,c.PRODUCT_ID,
-> sum(if(d.TYPE='ONSHOW',1,0)) as '上架数',
-> sum(if(d.TYPE='WITHDRAW',1,0)) as '下架数',
-> sum(if(d.TYPE='MOVE',1,0)) as '迁移数'
-> from chanage_apply c,chanage_cabinets d
-> where c.id=d.APPLICATION_ID
-> group by c.MRL,c.OPERATION_ID,c.PRODUCT_ID;
+------+--------------+------------+--------+--------+--------+
| MRL | OPERATION_ID | PRODUCT_ID | 上架数 | 下架数 | 迁移数 |
+------+--------------+------------+--------+--------+--------+
| 4 | 80 | 135 | 0 | 4 | 0 |
| 7 | 2 | 2 | 2 | 2 | 0 |
| 7 | 4 | 84 | 0 | 3 | 0 |
| 7 | 8 | 96 | 1 | 1 | 0 |
| 7 | 77 | 23 | 0 | 0 | 1 |
| 18 | 47 | 114 | 0 | 1 | 1 |
| 18 | 64 | 6 | 1 | 0 | 0 |
| 18 | 80 | 119 | 0 | 1 | 0 |
+------+--------------+------------+--------+--------+--------+
门户 一大把网站 6 10 15
门户 一大把邮箱 12 9 9
北京联通机房 财讯 财讯3.0 11 10 11
数商 数商3.0 22 12 12 门户 一大把企业邮局 21 12 12
广州七星岗机房 办公 投诉系统 11 12 20
数商 数商3.0 22 10 28 邮局 集中监控 20 21 30
香港机房 数商 数商Z邮箱 12 17 20 需求文档上给的是表格形式，像上面显示的那个格局。所以，我只往嵌套Map方面想了，用Hibernate查询后在业务层把查询出来的列表组装成一个嵌套map,
业务层查询处理后返回一个map: Map<String, List<Map<String, List<Map<String,IdcStatisticBO>>>>>
由外往里key分别是：机房名称、所属业务、项目名称
IdcStatisticBO里面存放那几个统计数值谁能处理嵌套这么多的map?? 如果可以，我明天贴出查询的方法，帮忙改正下