在日常开发中,会经常涉及到统计,肯定避免不了根据某些条件来确定是否统计该条记录。
mysql 结构如下
用户表
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`login_id` varchar(11) DEFAULT NULL COMMENT '登录id',
`name` varchar(255) DEFAULT NULL COMMENT '名字',
`age` int(11) DEFAULT NULL COMMENT '年龄',
`loves_string` varchar(255) DEFAULT NULL COMMENT '爱好',
`Address_id` varchar(11) DEFAULT NULL COMMENT '地址id',
`deleted` int(11) DEFAULT '0' COMMENT '0未删除,1已删除',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='用户表';
车表
CREATE TABLE `car` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`carName` varchar(255) DEFAULT NULL COMMENT '车名',
`userId` int(11) DEFAULT NULL COMMENT '所属人id',
`status` varchar(255) DEFAULT NULL COMMENT '状态:启用,停用',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='车表';
一、统计某个字段不同值对应的总数
1.1 根据《车表》的状态统计对应的数量
1.1.1最常规的 就是 if判断
SELECT
u.id,
u.`name`,
u.age,
sum(if(car.STATUS = '启用',1,0)) AS '启用的车数'
FROM
`user` u
LEFT JOIN car ON car.userId = u.id
WHERE
u.id IN ( 1, 2 )
GROUP BY
u.id
备注 if(表达式,true对应的值,false对应的值)
1.1.2 比较通用的就是case when
SELECT
u.id,
u.`name`,
u.age,
sum( case when car.STATUS = '启用' then 1 else 0 end ) AS '启用的车数'
FROM
`user` u
LEFT JOIN car ON car.userId = u.id
WHERE
u.id IN ( 1, 2 )
GROUP BY
u.id
备注:case when 表达式 then true对应的值 else 其他情况对应的值 end
1.1.3 我想不到的骚操作 or关键字
当我今天看到这样的一句代码,刚开始还在骂这统计的有问题,下一秒突然领悟到了它的精髓
SELECT
u.id,
u.`name`,
u.age,
count( car.STATUS = '启用' OR NULL ) AS '启用的车数'
FROM
`user` u
LEFT JOIN car ON car.userId = u.id
WHERE
u.id IN ( 1, 2 )
GROUP BY
u.id
备注:count( car.STATUS = ‘启用’ OR NULL ) 此处这个 or null 有妙用。
因为count 只会把非null的记录进行计数为1,所以要把所有 status不等于’启用’的记录的值都设置为null,这样count就会过滤掉那些不满足条件的记录(1 or null 是1 , 0 or null 是 null)