/* 表结构及初始化数据 */
CREATE TABLE `svn_log_file_info1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_account` varchar(50) DEFAULT NULL, /*用户名*/
  `filemodifytype` char(1) DEFAULT NULL, /*文件修改类型，'A'新增、'M'修改*/
  `size_add` bigint(20) DEFAULT NULL, /*添加代码的行数*/
  `size_mod` bigint(20) DEFAULT NULL, /*修改代码的行数*/
  `file_type` varchar(20) DEFAULT NULL, /*语言类型*/
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO svn_log_file_info1 VALUES(1,'zhangsan','A',12,2,'java');
INSERT INTO svn_log_file_info1 VALUES(2,'wangwu','A',32,54,'c');
INSERT INTO svn_log_file_info1 VALUES(3,'lisi','A',20,41,'java');
INSERT INTO svn_log_file_info1 VALUES(4,'zhangsan','M',33,72,'java');
INSERT INTO svn_log_file_info1 VALUES(5,'zhangsan','A',43,32,'c');
INSERT INTO svn_log_file_info1 VALUES(6,'wangwu','A',52,22,'java');
INSERT INTO svn_log_file_info1 VALUES(7,'lisi','M',42,26,'java');
INSERT INTO svn_log_file_info1 VALUES(8,'zhangsan','A',43,32,'js');
INSERT INTO svn_log_file_info1 VALUES(9,'wangwu','A',52,22,'c');
INSERT INTO svn_log_file_info1 VALUES(10,'lisi','M',42,26,'js');

SELECT log.user_account,sum(log.size_add + log.size_mod) code_size,
  (SELECT sum(size_add + size_mod) code_size FROM svn_log_file_info1  WHERE file_type='java' and user_account = log.user_account) java,
  (SELECT sum(size_add + size_mod) code_size FROM svn_log_file_info1  WHERE file_type='c' and user_account = log.user_account) C,
  (SELECT sum(size_add + size_mod) code_size FROM svn_log_file_info1  WHERE file_type not in('c','java') and user_account = log.user_account) other
    FROM svn_log_file_info1 log
    GROUP BY log.user_account;

select user_account,sum(size_add + size_mod) code_size,
  sum(if(file_type='java',size_add + size_mod,0)) as java,
  sum(if(file_type='C',size_add + size_mod,0)) as C,
  sum(if(file_type<>'java' and file_type<>'c',size_add + size_mod,0)) as other
from svn_log_file_info1
group by user_account

------解决方案--------------------

如果没有的显示为null，不显示为0，则把上面的0改为null即可
SQL code
select user_account,sum(size_add + size_mod) code_size,
  sum(if(file_type='java',size_add + size_mod,null)) as java,
  sum(if(file_type='C',size_add + size_mod,null)) as C,
  sum(if(file_type<>'java' and file_type<>'c',size_add + size_mod,null)) as other
from svn_log_file_info1
group by user_account

------解决方案--------------------
探讨

SQL code

/* 表结构及初始化数据 */
CREATE TABLE `svn_log_file_info1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_account` varchar(50) DEFAULT NULL, /*用户名*/
  `filemodifytype` char(1) DEFAULT NULL, /*……


------解决方案--------------------
MYSQL里面IF是三元运算符 相当于MSSQL里面的
CASE WHEN ..THEN ..ELSE ..END
可以写成1楼树锅的格式。然后再user_account字段加上索引。

------解决方案--------------------
SQL code

select user_account,sum(size_add + size_mod) code_size,
       sum(if(file_type='java',size_add + size_mod,0)) as java,
       sum(if(file_type='C',size_add + size_mod,0))as C,
       sum(if(file_type<>'java' and file_type<>'c',size_add + size_mod,0)) as other
from svn_log_file_info1
--树锅这个写法把你三个子查询给优化了。
--去掉三次子查询这个速度你可以目睹了。
group by user_account