求sql语句，按多个字段分组统计

机柜变更单执行表
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表的ID

OPERATION_ID    所属业务

PRODUCT_ID      产品ID

MRL             所在机房

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

关联关系说明：
一个业务有多个产品，一个产品只属于一个业务，每个业务可能对各个机房作了机柜变更，因为每个业务可能在每个机房都有机柜，机柜上放置的是各个业务的服务器。各个业务不定期的会在各机房作上架、下架、迁移操纵，这里上架下架迁移指的是服务器上架到机柜、从机柜下架、从一个机柜迁移到另一个机柜

11 个解决方案

#1

上面的关联关系说明没换行，如下：

关联关系说明：
一个业务有多个产品，一个产品只属于一个业务，每个业务可能对各个机房作了机柜变更，
因为每个业务可能在每个机房都有机柜，机柜上放置的是各个业务的服务器。
各个业务不定期的会在各机房作上架、下架、迁移操纵，
这里上架下架迁移指的是服务器上架到机柜、从机柜下架、从一个机柜迁移到另一个机柜

#2

建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。

否则只看你的文字描述，很容易出现理解偏差。

#3

本想试着理解一下的，看来猜不出你的数据关系。

select c.MRL,c.OPERATION_ID,c.PRODUCT_ID,
	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

#4

贴数据及要求结果出来看看，简要说明一下结果是怎样得出的，这样直观一些

#5

说明：chanage_apply里面的chanage_type不需要，因为chanage_type是以、分割的字段，是包含关系而非等于关系，不能限定它为‘CABI、’。
这里要以另外两张表为主，连接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');

#6

计算：
按机房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

用你提供的测试数据做了一下，但为什么你的结果中没有
|    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 |
+------+--------------+------------+--------+--------+--------+

#8

这个不是分组行列转换的典型问题么？

#9

就是交叉表嘛　，有什么问题？

#10

引用 7 楼 ACMAIN_CHM 的回复:

用你提供的测试数据做了一下，但为什么你的结果中没有 
|    7 |            8 |        96 |      1 |      1 |      0 | 
|    7 |          77 |        23 |      0 |      0 |      1 | 
这两组数据？ 

哦，程序整的我头晕，这些测试数据还是从数据库巨多数据中摘出来的，说明的结果中写漏了

程序结果要求显示在JSP页面是像下面这样（怎么不能传图片上来呢） 

机房名称              所属业务             项目名称                 上架数         下架数          迁移数
                        门户               一大把网站                 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?? 如果可以，我明天贴出查询的方法，帮忙改正下

#11

Hibernate 东西，你需要到java版去问了。