继续我们的SQL能力测试专题，今天的题目如下：
SQL二：用户表（包含字段有：用户ID[自增]、姓名、性别、民族、出生日期、身份证号）
采用一个SQL语句，查询出：
用户总数，男性人数，女性人数， 民族是汉族的人数，民族是少数民族(非汉族)的人数，出生日期是1995年的人数，没有身份证号的人数
首先看题目就感觉不是很难，但是细节还是很多，首先我们先考虑用那种，一看到根据某一条件求总数就想到了采用CASE…WHEN…的形式，因此我们首先采用这种方式：
在开始之前，我们要首先要明白case…when…的简单搜索方式和函数搜索方式：
SELECT COUNT(*) AS person_all_sum , CASE user_sex_code WHEN 1 THEN '男' ELSE '女' END CASE; FROM `t_user_info` -------------------------------------------------------------- SELECT CASE user_sex_code WHEN 1 THEN '男' ELSE '女' END FROM `t_user_info` -------------------------------------------------------------- SELECT CASE WHEN user_sex_code = 1 THEN '男' WHEN user_sex_code = 2 THEN '女' ELSE '其他' END FROM `t_user_info`
链接: SQL中的case when then else end用法.
结合上面问题写出来的SQL如下：
SELECT SUM( CASE WHEN user_sex_code = 1 THEN 1 ELSE 0 END ) AS man_sum, SUM( CASE WHEN user_sex_code = 2 THEN 1 ELSE 0 END ) AS woman_sum, SUM( CASE WHEN user_nation = 1 THEN 1 ELSE 0 END ) AS hannation_sum, SUM( CASE WHEN user_nation <> 1 THEN 1 ELSE 0 END ) AS unhannation_sum, SUM( CASE WHEN DATE_FORMAT( user_birthday, '%Y%' ) = 1995 THEN 1 ELSE 0 END ) AS birthday_sum, SUM( CASE WHEN user_idcard IS NULL OR user_idcard = '' THEN 1 ELSE 0 END ) AS unuser_idcard_sum, SUM( CASE WHEN user_idcard IS NOT NULL AND user_idcard <> '' THEN 1 ELSE 0 END ) AS user_idcard_sum FROM `t_user_info`
在这里针对细节进行一下分析：
1、 首先我们一定要采用<>来进行不等于判断，虽然采用 != 也是可以的，但是经过测试发现mysql中用<>与!=都是可以的，但sqlserver中不识别!=,所以建议用<>
只有<>是标准的sql语法，可以移植，其他的都是oracle平台的特性，移植性较差，所以开发中，尽量使用<>表示不等于只有<>是标准的sql语法，可以移植，其他的都是oracle平台的特性，移植性较差，所以开发中，尽量使用<>表示不等于。
2、 用isnull判断是否为空：只有name 为null 的时候 ISNULL(exp) 函数的返回值为1 ，空串和有数据都为0；
过滤到is null的sql语句 还用可以用如下 (同时剔除null 和 空字符串 ):
SUM( CASE WHEN ( ISNULL( user_idcard )= 1 ) || ( LENGTH( trim( user_idcard ))= 0 ) THEN 1 ELSE 0 END ) AS emptyidcord_sum,-- 获取空身份证人数
3、SUM中只要给出表示真值的逻辑条件即可，所以我们可以把以上的SQL语句改成如下：
select 100=100;
select 100=200;
select DATE(NOW())=‘2021-08-13’;
select DATE(NOW())=‘2021-10-30’;
SELECT SUM( user_sex_code = 1 ) AS man_sum, SUM( user_sex_code = 2 ) AS woman_sum, SUM( user_nation = 1 ) AS hannation_sum, SUM( user_nation <> 1) AS unhannation_sum, SUM( DATE_FORMAT( user_birthday, '%Y%' ) = 1995 ) AS birthday_sum, SUM( user_idcard IS NULL OR user_idcard = '' ) AS unuser_idcard_sum, SUM( user_idcard IS NOT NULL AND user_idcard <> '' ) AS user_idcard_sum FROM `t_user_info`
到这里其实我们还要进行一个优化，我们这里为birthday加上普通索引，来看一下，然后执行不同的sql进行一下对比，对于MySql索引的知识详见如下：MySql索引：MySql索引知识学习
在这里插入图片描述
我们分别执行：
EXPLAIN SELECT * FROM t_user_info
与
EXPLAIN SELECT * FROM t_user_info WHERE user_birthday LIKE "1995%"
两个SQL语句， 一般情况下不是鼓励使用like,如果非使用，那么需要注意 like"%aaa%"不会使用索引；但like“aaa%”会使用索引。可以采用以下两条语句进行一下对比：
EXPLAIN SELECT * FROM t_user_info WHERE user_birthday LIKE "1995%" EXPLAIN SELECT * FROM t_user_info WHERE user_birthday >= '1995-01-01 00:00:00'AND user_birthday <= '1995-12-31 23:59:59'
注意：能不用函数的时候就不用函数，因为函数导致索引失效
-- 大神级别 精简模式 EXPLAIN SELECT count(*) AS user_all_num,-- 获取总人数 SUM( user_sex = '2') AS woman_sum,-- 获取女生人数 SUM( user_sex = '1') AS man_sum,-- 获取男生人数 SUM( user_nation = '1' ) AS hannation_sum,-- 获取汉族人数 SUM( user_nation <> '1' ) AS unhannation_sum,-- 获取非汉族人数 SUM( user_birthday >= '1995-01-01 00:00:00' AND user_birthday <= '1995-12-31 23:59:59' ) AS birt_sum,-- 获取出生日期为1995年的人数 SUM( user_idcard is null OR user_idcard = '' ) AS emptyidcord_sum,-- 获取空身份证人数 SUM( user_idcard <> '' ) AS notemptyidcord_sum-- 获取非空身份证人数 FROM `t_user_info`
欢迎感兴趣的小伙伴一起探讨学习知识，以上是个人的一些总结分享，如有错误的地方望各位留言指出，十分感谢。
觉得有用的话别忘点赞、收藏、关注，手留余香！ 😗 😗 😗
