按照某一列统计数据

来源：互联网发布：java 字符串特殊符号编辑：程序博客网时间：2024/05/21 17:12

按照某一列统计数据

具体需求如下：

要统计一个数据表中的数据，将某一列的每个相同的值加和，作为一个新的列。

具体数据如下：

id   result1       22       23       14       25       36       1

统计结果如下,大概意思吧。

id          result1   result2   result31               2       3           1

其中id关联别的表
具体的SQL如下，仅供参考：

SELECT c.agentName, c.agentCompany,SUM(IF(a1.id is not null,1,0)) as 't1',SUM(IF(a2.id is not null,1,0)) as 't2',SUM(IF(a3.id is not null,1,0)) as 't3',SUM(IF(a4.id is not null,1,0)) as 't4'from agentorder b INNER JOIN agent c on b.agentId = c.idLEFT JOIN orderlog a1 on a1.orderId = b.id and a1.createTime >= '2016-06-01 00:00:00' and a1.createTime <= '2016-06-30 23:59:59' and a1.checkStep = '3000'LEFT JOIN orderlog a2 on a2.orderId = b.id and a2.createTime >= '2016-06-01 00:00:00' and a2.createTime <= '2016-06-30 23:59:59' and a2.checkStep = '3001'LEFT JOIN orderlog a3 on a3.orderId = b.id and a3.createTime >= '2016-06-01 00:00:00' and a3.createTime <= '2016-06-30 23:59:59' and a3.checkStep = '6000'LEFT JOIN orderlog a4 on a4.orderId = b.id and a4.createTime >= '2016-06-01 00:00:00' and a4.createTime <= '2016-06-30 23:59:59' and a4.checkStep = '2000'GROUP BY c.agentName

方法2SELECT b.agentName as '房管员', b.agentCompany as '公司',(select count(*) from orderlog a1, order b1 where a1.orderId = b1.id and a1.createTime >= '2016-06-01 00:00:00' and a1.createTime <= '2016-06-30 23:59:59' and a1.checkStep = '3000' and b1.agentId =b.id) as '重传合同',(select count(*) from orderlog a2, order b2 where a2.orderId = b2.id and a2.createTime >= '2016-06-01 00:00:00' and a2.createTime <= '2016-06-30 23:59:59' and a2.checkStep = '3001' and b2.agentId =b.id) as '重传身份证',(select count(*) from orderlog a3, order b3 where a3.orderId = b3.id and a3.createTime >= '2016-06-01 00:00:00' and a3.createTime <= '2016-06-30 23:59:59' and a3.checkStep = '6000' and b3.agentId =b.id) as '清退',(select count(*) from orderlog a4, order b4 where a4.orderId = b4.id and a4.createTime >= '2016-06-01 00:00:00' and a4.createTime <= '2016-06-30 23:59:59' and a4.checkStep = '3000' and b4.agentId =b.id) as '推单'from agent b

方法3SELECT b.agentName as '房管员', b.agentCompany as '公司',(select count(*) from orderlog a1, order a2 where a1.orderId = a2.id and a1.createTime >= '2016-06-01 00:00:00' and a1.createTime <= '2016-06-30 23:59:59' and a1.checkStep = '3000' and a2.agentId =b.id) as '重传合同',(select count(*) from orderlog a1, order a2 where a1.orderId = a2.id and a1.createTime >= '2016-06-01 00:00:00' and a1.createTime <= '2016-06-30 23:59:59' and a1.checkStep = '3001' and a2.agentId =b.id) as '重传身份证',(select count(*) from orderlog a1, order a2 where a1.orderId = a2.id and a1.createTime >= '2016-06-01 00:00:00' and a1.createTime <= '2016-06-30 23:59:59' and a1.checkStep = '6000' and a2.agentId =b.id) as '清退',(select count(*) from orderlog a1, order a2 where a1.orderId = a2.id and a1.createTime >= '2016-06-01 00:00:00' and a1.createTime <= '2016-06-30 23:59:59' and a1.checkStep = '3000' and a2.agentId =b.id) as '推单'from agent b

0 0