select DATE_FORMAT(`adddate`,'%Y-%m-%d'), sum(if(DATE_FORMAT(`adddate`,'%H')='0',1,0)) as 1, sum(if(DATE_FORMAT(`adddate`,'%H')='1',1,0)) as 2, .... sum(if(DATE_FORMAT(`adddate`,'%H')='23',1,0)) as 24 from tt group by DATE_FORMAT(`adddate`,'%Y-%m-%d')
INSERT INTO `e_workrole` VALUES ('113aa0fce1f5001f1217', '0', '0', '2010-11-22 10:41:19', 'test01', '1', '', null, '', '95599', null, '95599', '20101122104119421', null, null, '/qqt/', '10:41:19', '', '处理方式1', '20101122104119421', '问题分类1', '', null, null, '1', '0', '', '2010-11-22', '21817773-153a-4540-91a0-d234d4cb56ea', null, null);
INSERT INTO `e_workrole` VALUES ('11414d3dbb4000101273', '0', '0', '2011-02-25 16:42:55', 'test01', '1', '', null, '张三', '11103', null, '11103', '20110225164253859', null, null, '/qqt/', '16:42:53', '', '处理方式1', '20110225164253859', '问题分类1', '', null, null, '1', '0', '张三', '2011-02-25', '03cca059-6eef-4b0d-bdba-06174dd28067', null, null);
INSERT INTO `e_workrole` VALUES ('217ad802b34000101273', '0', '0', '2011-02-25 15:44:01', 'test01', '1', '', null, '张三', '11103', null, '11103', '20110225154400609', null, null, '/qqt/', '15:44:00', '', '处理方式1', '20110225154400609', '问题分类1', '', null, null, '1', '0', '张三', '2011-02-25', 'dc65ffa7-68e5-49df-84fb-59d129a1223d', null, null);
INSERT INTO `e_workrole` VALUES ('319dc775dd4e00101c45', '0', '0', '2011-03-15 16:23:09', 'test03', '1', '', '2011-03-15 16:23:13', '55', '11104', null, '11104', '20110315162308703', null, null, '/qqt/', '16:23:08', '', '处理方式1', '20110315162308703', '问题分类1', '', null, null, '1', '0', '55', '2011-03-15', 'ab5192db-37dc-4804-b305-af2286fb813c', null, null);
INSERT INTO `e_workrole` VALUES ('31bb1971194300101456', '0', '0', '2011-02-28 17:02:17', 'test03', '1', '', null, '张三', '1003', null, '1003', '20110228170216468', null, null, '/qqt/', '17:02:16', '', '处理方式1', '20110228170216468', '问题分类1', '', null, null, '1', '0', '张三', '2011-02-28', '7ab6297f-944f-4a19-8f17-4ddb06587cbd', null, null);
INSERT INTO `e_workrole` VALUES ('4177bab00dee001f12fe', '0', '0', '2010-11-12 11:34:01', 'test01', '1', '', null, '3222', '1004', null, '1004', '20101112113400296', null, null, '/qqt/', '11:34:00', '', '处理方式1', '20101112113400296', '问题分类1', '', null, null, '1', '0', '3222', '2010-11-12', '353637f4-76c0-41c1-84aa-314a22088b85', null, null);
INSERT INTO `e_workrole` VALUES ('41d04a72aef7001f118c', '0', '0', '2010-11-24 17:37:25', 'test01', '1', '', null, '', '11112', null, '11112', '20101124173724671', null, null, '/qqt/', '17:37:24', '', '处理方式1', '20101124173724671', '问题分类1', '', null, null, '1', '0', '', '2010-11-24', '235faf92-1ad4-473d-8691-27dee3c218ab', null, null);
INSERT INTO `e_workrole` VALUES ('508f7f1124ee001f12fe', '0', '0', '2010-11-12 14:14:12', 'test01', '1', '', null, '3222ff', '1004', null, '1004', '20101112141411703', null, null, '/qqt/', '14:14:11', '', '处理方式1', '20101112141411703', '问题分类1', '', null, null, '1', '0', '3222ff', '2010-11-12', 'c2244dc5-455d-4e39-9a11-55f82a6b974f', null, null);
INSERT INTO `e_workrole` VALUES ('51a53f2823ee001f12fe', '0', '0', '2011-03-15 14:07:41', 'test01', '1', '', null, '3222ff', '1004', null, '1004', '20101112140740359', null, null, '/qqt/', '14:07:40', '', '处理方式1', '20101112140740359', '问题分类1', '', null, null, '1', '0', '3222ff', '2010-11-12', 'c7a3f722-b925-4106-b1da-c868f0b4e8d0', null, null);
INSERT INTO `e_workrole` VALUES ('51dbf813dd4e00101c45', '0', '0', '2011-03-15 16:20:25', 'test03', '1', '', null, '55', '11104', null, '11104', '20110315162024546', null, null, '/qqt/', '16:20:24', '', '处理方式1', '20110315162024546', '问题分类1', '', null, null, '1', '0', '55', '2011-03-15', '961c7d13-4348-4fe4-a1b5-c2cc3b5205a7', null, null);
INSERT INTO `e_workrole` VALUES ('6137d830174300101928', '0', '0', '2011-02-28 16:46:10', 'test03', '1', '', null, '张三', '1003', null, '1003', '20110228164609843', null, null, '/qqt/', '16:46:09', '', '处理方式1', '20110228164609843', '问题分类1', '', null, null, '1', '0', '张三', '2011-02-28', 'a749d952-55db-4d67-8fb9-5a90621a293a', null, null);
INSERT INTO `e_workrole` VALUES ('61c140b9b24000101273', '0', '0', '2011-02-25 15:41:57', 'test01', '1', '', null, '张三', '11103', null, '11103', '2011022515415778', null, null, '/qqt/', '15:41:57', '', '处理方式1', '2011022515415778', '问题分类1', '', null, null, '1', '0', '张三', '2011-02-25', '304be1ce-9fee-450d-b7fc-14f34fa8d037', null, null);
INSERT INTO `e_workrole` VALUES ('61e726a50dee001f12fe', '0', '0', '2010-11-12 11:33:41', 'test01', '1', '', null, '3222', '1004', null, '1004', '201011121133410', null, null, '/qqt/', '11:33:41', '', '处理方式1', '201011121133410', '问题分类1', '', null, null, '1', '0', '3222', '2010-11-12', '4ace9660-cb65-439c-bccd-d31d5d01c4a0', null, null);
INSERT INTO `e_workrole` VALUES ('716cd3c8e1f5001f1217', '0', '0', '2010-11-22 10:39:52', 'test01', '1', '', null, '张三', '13951019643', null, '13951019643', '20101122103948125', null, null, '/qqt/', '10:39:48', '', '处理方式1', '20101122103948125', '问题分类1', '', null, null, '1', '0', '张三', '2010-11-22', 'cad4ed8d-6eff-4872-bb3a-f4038da2ce19', null, null);
INSERT INTO `e_workrole` VALUES ('71d097d8ae4000101273', '0', '0', '2011-02-25 15:14:12', 'test01', '1', '', null, '张三', '11103', null, '11103', '20110225151411734', null, null, '/qqt/', '15:14:11', '', '处理方式1', '20110225151411734', '问题分类1', '', null, null, '1', '0', '张三', '2011-02-25', 'd4701249-dd74-40e7-935c-288fa2ed230e', null, null);
INSERT INTO `e_workrole` VALUES ('81b169e9baf7001f1c40', '0', '0', '2010-11-24 19:06:39', 'test01', '1', '', null, '', '1004', null, '1004', '20101124190638453', null, null, '/qqt/', '19:06:38', '', '处理方式1', '20101124190638453', '问题分类1', '', null, null, '1', '0', '张三', '2010-11-24', '6a9294dc-929c-4825-8b2a-c9e67e081f4f', null, null);
INSERT INTO `e_workrole` VALUES ('917b11e51443001018d7', '0', '0', '2011-02-28 16:29:44', 'test03', '1', '', null, '张三', '1003', null, '1003', '20110228162943140', null, null, '/qqt/', '16:29:43', '', '处理方式1', '20110228162943140', '问题分类1', '', null, null, '1', '0', '张三', '2011-02-28', '1ec261f9-b858-4c61-8c69-fe65e69637bf', null, null);
INSERT INTO `e_workrole` VALUES ('91a7d4631543001018d7', '0', '0', '2011-02-28 16:33:16', 'test03', '1', '', null, '张三', '1003', null, '1003', '20110228163316250', null, null, '/qqt/', '16:33:16', '', '处理方式1', '20110228163316250', '问题分类1', '', null, null, '1', '0', '张三', '2011-02-28', 'a79e6181-6dfd-4fbb-a21a-a4f15f209087', null, null);
INSERT INTO `e_workrole` VALUES ('a17d04dfdc4e00101c45', '0', '0', '2011-03-15 16:18:56', 'test03', '1', '', null, '55', '11104', null, '11104', '20110315161838937', null, null, '/qqt/', '16:18:38', '', '处理方式1', '20110315161838937', '问题分类1', '', null, null, '1', '0', '55', '2011-03-15', '90656ac6-465f-4966-92d3-f23c7333224d', null, null);
INSERT INTO `e_workrole` VALUES ('a18e68d1ba4000101273', '0', '0', '2011-02-25 16:39:54', 'test01', '1', '', null, '张三', '11103', null, '11103', '20110225163947609', null, null, '/qqt/', '16:39:47', '', '处理方式1', '20110225163947609', '问题分类1', '', null, null, '1', '0', '张三', '2011-02-25', 'c7709931-8942-494c-bb84-a8a6eaaa1213', null, null);
INSERT INTO `e_workrole` VALUES ('a1b1a523b34000101273', '0', '0', '2011-02-25 15:44:56', 'test01', '1', '', null, '张三', '11103', null, '11103', '20110225154455578', null, null, '/qqt/', '15:44:55', '', '处理方式1', '20110225154455578', '问题分类1', '', null, null, '1', '0', '张三', '2011-02-25', '4796eb17-bbf5-4525-a3e4-a28f5f425b22', null, null);
INSERT INTO `e_workrole` VALUES ('b1327f747df7001f1d1c', '0', '0', '2010-11-24 11:46:44', 'test01', '1', '', null, '张三', '1004', null, '1004', '20101124114643640', null, null, '/qqt/', '11:46:43', '', '处理方式1', '20101124114643640', '问题分类1', '', null, null, '1', '0', '张三', '2010-11-24', '5b0cd11e-3cbb-4792-8871-a6301a9b3ebb', null, null);
INSERT INTO `e_workrole` VALUES ('d14cbb1b23ee001f12fe', '0', '0', '2010-11-12 14:07:20', 'test01', '1', '', null, '3222', '1004', null, '1004', '2010111214071993', null, null, '/qqt/', '14:07:19', '', '处理方式1', '2010111214071993', '问题分类1', '', null, null, '1', '0', '3222', '2010-11-12', 'fdb6045f-bf7d-416b-94c0-0d822a7a473f', null, null);
INSERT INTO `e_workrole` VALUES ('d185ff69164300101928', '0', '0', '2011-02-28 16:40:36', 'test03', '1', '', null, '张三', '1003', null, '1003', '20110228164035984', null, null, '/qqt/', '16:40:35', '', '处理方式1', '20110228164035984', '问题分类1', '', null, null, '1', '0', '张三', '2011-02-28', '7f9bc59a-df9c-4bf7-a879-b8d6990446b7', null, null);
INSERT INTO `e_workrole` VALUES ('d1d3926124ee001f12fe', '0', '0', '2010-11-12 14:16:26', 'test01', '1', '', null, '3222ff', '1004', null, '1004', '2010111214162662', null, null, '/qqt/', '14:16:26', '', '处理方式1', '2010111214162662', '问题分类1', '', null, null, '1', '0', '3222ff', '2010-11-12', '3548f1fc-2401-4302-8f0f-952bb6b312bb', null, null);
INSERT INTO `e_workrole` VALUES ('e120df58ae4000101273', '0', '0', '2011-02-25 15:10:38', 'test01', '1', '', null, '张三', '11103', null, '11103', '2011022515103778', null, null, '/qqt/', '15:10:37', '', '处理方式1', '2011022515103778', '问题分类1', '', null, null, '1', '0', '张三', '2011-02-25', '19089b56-2c71-4da4-ad22-973227a608ea', null, null);
INSERT INTO `e_workrole` VALUES ('e17890a5b24000101273', '0', '0', '2011-02-25 15:41:24', 'test01', '1', '', null, '张三', '11103', null, '11103', '20110225154123718', null, null, '/qqt/', '15:41:23', '', '处理方式1', '20110225154123718', '问题分类1', '', null, null, '1', '0', '张三', '2011-02-25', '6de5c36d-947e-46e0-bc64-13969b4d194c', null, null);
INSERT INTO `e_workrole` VALUES ('f1ea6fd309f6001f1d30', '0', '0', '2010-11-22 15:26:30', 'test01', '1', '', null, '3222ff', '1004', null, '1004', '20101122152629437', null, null, '/qqt/', '15:26:29', '', '处理方式1', '20101122152629437', '问题分类1', '', null, null, '1', '0', '3222ff', '2010-11-22', '2e69a205-3fa4-403a-a440-f5765d3fcf63', null, null);要求统计出如下格式的数据
时间 1, 2, 3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24
2011-03-14 0,0
2011-03-15
行为第月的每天，列为每天的整点时间数，以adddate为统计对象，分别统计出每天每个小时段它有多少条记录数。
有点难度
[/code]
sum(if(DATE_FORMAT(`adddate`,'%H')='0',1,0)) as 1,
sum(if(DATE_FORMAT(`adddate`,'%H')='1',1,0)) as 2,
....
sum(if(DATE_FORMAT(`adddate`,'%H')='23',1,0)) as 24
from tt group by DATE_FORMAT(`adddate`,'%Y-%m-%d')
-> sum(if(hour(adddate)=0,1,0)) as `01`,
-> sum(if(hour(adddate)=1,1,0)) as `02`,
-> sum(if(hour(adddate)=2,1,0)) as `03`,
-> sum(if(hour(adddate)=3,1,0)) as `04`,
-> sum(if(hour(adddate)=4,1,0)) as `05`,
-> sum(if(hour(adddate)=5,1,0)) as `06`,
-> sum(if(hour(adddate)=6,1,0)) as `07`,
-> sum(if(hour(adddate)=7,1,0)) as `08`,
-> sum(if(hour(adddate)=8,1,0)) as `09`,
-> sum(if(hour(adddate)=9,1,0)) as `10`,
-> sum(if(hour(adddate)=10,1,0)) as `11`,
-> sum(if(hour(adddate)=11,1,0)) as `12`,
-> sum(if(hour(adddate)=12,1,0)) as `13`,
-> sum(if(hour(adddate)=13,1,0)) as `14`,
-> sum(if(hour(adddate)=14,1,0)) as `15`,
-> sum(if(hour(adddate)=15,1,0)) as `16`,
-> sum(if(hour(adddate)=16,1,0)) as `17`,
-> sum(if(hour(adddate)=17,1,0)) as `18`,
-> sum(if(hour(adddate)=18,1,0)) as `19`,
-> sum(if(hour(adddate)=19,1,0)) as `10`,
-> sum(if(hour(adddate)=20,1,0)) as `21`,
-> sum(if(hour(adddate)=21,1,0)) as `22`,
-> sum(if(hour(adddate)=22,1,0)) as `23`,
-> sum(if(hour(adddate)=23,1,0)) as `24`
-> from e_workrole
-> group by date(adddate);
+------------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
| DATE | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 10 | 21| 22 | 23 | 24 |
+------------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
| 2010-11-12 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 3 | 0 | 0 | 0 | 0 | 0 | 0| 0 | 0 | 0 |
| 2010-11-22 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0| 0 | 0 | 0 |
| 2010-11-24 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 0| 0 | 0 | 0 |
| 2011-02-25 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 6 | 2 | 0 | 0 | 0 | 0| 0 | 0 | 0 |
| 2011-02-28 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 4 | 1 | 0 | 0 | 0| 0 | 0 | 0 |
| 2011-03-15 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 3 | 0 | 0 | 0 | 0| 0 | 0 | 0 |
+------------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
6 rows in set (0.00 sec)mysql>