【MySQL】条件判断函数 case when 求和计数,if 函数
比赛结果result表内容如下:
DateWin2017-07-12 胜2017-07-12 负2017-07-15 胜2017-07-15 负
如果要生成下列结果, 正确的sql语句是:( )
比赛日期 胜 负2017-07-12 1 12017-07-15 1 1
select Date As 比赛日期, (case when Win='胜' then 1 else 0 end) 胜, (case when Win='负' then 1 else 0 end) 负 from result group by Dateselect Date As 比赛日期, SUM(case when Win='胜' then 1 else 0 end) 胜, SUM(case when Win='负' then 1 else 0 end) 负 from resultselect Date As 比赛日期, SUM( when Win='胜' then 1 else 0 end) 胜, SUM( when Win='负' then 1 else 0 end) 负 from result group by Dateselect Date As 比赛日期, SUM(case when Win='胜' then 1 else 0 end) 胜, SUM(case when Win='负' then 1 else 0 end) 负 from result group by Date
解析:
由题意得,需要行转列按日期分组并对列求和,因此需要 group by Date, sum(…胜/负)。
- A 中无 sum ,错;
- B 中无 group by ,错;
- C 中 case when … then … else … end 用法不正确,错;
- 选 D
这道题主要考察了 case when 的用法,下面是使用方法:
1、case when… then… else… end:
CASE 字段 WHEN 预期值 THEN 结果1 ELSE 结果2 END
score 表:
mysql> select * from score;+----+----------+------+------+-------+| id | name | sex | age | score |+----+----------+------+------+-------+| 1 | zhangsan | 1 | 19 | 99 || 2 | lisi | 0 | 25 | 89 || 3 | wangwu | 1 | 21 | 79 || 4 | zhaoliu | 0 | 18 | 69 || 5 | sunqi | 1 | 23 | 59 || 6 | zhouba | 1 | 15 | 49 || 7 | wujiu | 0 | 20 | 39 |+----+----------+------+------+-------+
示例: 查找男女,以汉字显示
1、使用 case when
,符合要求的返回 1,case when
外部用 sum
进行求和
select name, (case sex when '1' then '男' else '女' end) sex from score;
2、用 if
函数的方式:
select name, if(sex = 1, '男', '女') as sex from score;
+----------+-----+| name | sex |+----------+-----+| zhangsan | 男 || lisi | 女 || wangwu | 男 || zhaoliu | 女 || sunqi | 男 || zhouba | 男 || wujiu | 女 |+----------+-----+
2、条件表达式:
CASE WHEN condition THEN result1 ELSE result2END
- 如果没有
end
,返回NULL
- 注意: 多个
when
之间,不必加逗号
示例 1: 统计每个学生的成绩级别
select name, score, (case when score > 90 then 'nice' when score > 80 then 'good' when score >= 60 then'not bad' else 'bad' end) as level from score;
示例 2: 统计总人数,男女人数和男女成年的人数
1、case when
外部用 sum
进行求和
select count(id) as '总人数', sum(case when sex = '1' then 1 else 0 end) as '男生人数', sum(case when sex = '0' then 1 else 0 end) as '女生人数', sum(case when age >= 18 and sex = '1' then 1 else 0 end) as '成年男生人数', sum(case when age >= 18 and sex = '0' then 1 else 0 end) as '成年女生人数' from score;
2、if
函数计数
select count(if(age >= 18 and sex = 1, 1, null)) as '成年男生人数', count(if(age >= 18 and sex = 0, 1, null)) as '成年女生人数' from score;
+-----------+--------------+--------------+--------------------+--------------------+| 总人数 | 男生人数 | 女生人数 | 成年男生人数| 成年女生人数|+-----------+--------------+--------------+--------------------+--------------------+| 7 | 4 | 3 | 3 | 3 |+-----------+--------------+--------------+--------------------+--------------------+
多事通