1 缘起
补习MySQL基础函数应用。
我的SQL应用太烂了，相对复杂的功能就要各种问和搜索，
先从基础的开始补习，
分享如下，文末附建表和添加数据。
2 原始数据
3 行转列
将subject转为按列显示，效果如下图所示。
思路：
（1）提取出subject列，按username为科目列取score列的值。
（2）将相同username的行求和，获取每个username的subject分数（score）。
（3）最后按照username分组。
3.1 使用CASE
SELECT username,
SUM(CASE subject WHEN '科目一' THEN score ELSE 0 END) AS '科目一',
SUM(CASE subject WHEN '科目二' THEN score ELSE 0 END) AS '科目二',
SUM(CASE subject WHEN '科目三' THEN score ELSE 0 END) AS '科目三',
SUM(CASE subject WHEN '科目四' THEN score ELSE 0 END) AS '科目四'
FROM db_monkey_run.tb_user_score
GROUP BY username;
3.2 使用IF
SELECT username,
SUM(IF(subject='科目一', score, 0)) AS '科目一',
SUM(IF(subject='科目二', score, 0)) AS '科目二',
SUM(IF(subject='科目三', score, 0)) AS '科目三',
SUM(IF(subject='科目四', score, 0)) AS '科目四'
FROM db_monkey_run.tb_user_score
GROUP BY username;
4 汇总数据
4.1 使用SUM
- IF行转列，SUM汇总
SELECT username,
SUM(IF(subject='科目一', score, 0)) AS '科目一',
SUM(IF(subject='科目二', score, 0)) AS '科目二',
SUM(IF(subject='科目三', score, 0)) AS '科目三',
SUM(IF(subject='科目四', score, 0)) AS '科目四',
SUM(score) AS total
FROM db_monkey_run.tb_user_score
GROUP BY username
ORDER BY total DESC;
- CASE行转列，SUM汇总
-- 行转列，汇总
SELECT username,
SUM(CASE subject WHEN '科目一' THEN score ELSE 0 END) AS '科目一',
SUM(CASE subject WHEN '科目二' THEN score ELSE 0 END) AS '科目二',
SUM(CASE subject WHEN '科目三' THEN score ELSE 0 END) AS '科目三',
SUM(CASE subject WHEN '科目四' THEN score ELSE 0 END) AS '科目四',
SUM(score) AS total
FROM db_monkey_run.tb_user_score
GROUP BY username
ORDER BY total DESC;
4.2 使用WITH ROLLUP
步骤：
（1）子查询：计算每个username各个subject的总和。
（2）行转列：计算每个username同一个subject的总和。
SELECT IFNULL(username, 'total') AS username,
SUM(IF(subject='科目一', score, 0)) AS '科目一',
SUM(IF(subject='科目二', score, 0)) AS '科目二',
SUM(IF(subject='科目三', score, 0)) AS '科目三',
SUM(IF(subject='科目四', score, 0)) AS '科目四',
SUM(IF(subject='total', score, 0)) AS total
FROM(
SELECT username, IFNULL(subject, 'total') AS subject, SUM(score) AS score
FROM db_monkey_run.tb_user_score
GROUP BY username, subject
WITH ROLLUP
HAVING username IS NOT NULL
) AS A
GROUP BY username
WITH ROLLUP;
5 数据拼接
SELECT username, GROUP_CONCAT(subject, ':', score) AS score
FROM db_monkey_run.tb_user_score
GROUP BY username;
6 小结
（1）行转列两种方案：CASE和IF；
（2）汇总两种方案：SUM和WITH ROLLUP；
（3）拼接：GROUP_CONCAT。
7 函数使用
7.1 IF
格式：
IF(exp, v1, v2)
如果exp为true，使用v1替换，exp为false，使用v2替换。
| 序号 | 参数 | 描述 |
|---|---|---|
| 1 | exp | 表达式 |
| 2 | v1 | 表达式为true时赋予的值 |
| 3 | v2 | 表达式为false时赋予的值 |
SELECT id, user_id, IF(sex IS NULL, 'UNKNOWN', sex) AS sex FROM db_monkey_run.tb_user;
7.2 IFNULL
格式：
IFNULL(v1,v2)
如果v1值为NULL，使用v2替换NULL。
| 序号 | 参数 | 描述 |
|---|---|---|
| 1 | v1 | 查询的值，如果为NULL，使用v2 |
| 2 | v2 | 替换NULL的值 |
SELECT id, user_id, IFNULL(sex, 'UNKNOWN') AS sex FROM db_monkey_run.tb_user;
7.3 CASE
格式：
CASE column
WHEN v1 THEN v2
ELSE v3
END
如果column值为v1，使用v2替换，否则使用v3替换。
SELECT
id,
user_id,
CASE sex
WHEN 'male' THEN '男'
WHEN 'female' THEN '女'
ELSE 'UNKNOWN'
END AS sex
FROM
db_monkey_run.tb_user;
7.4 DISTINCT
合并相同数据值，NULL也作为一种数据。
SELECT DISTINCT sex FROM db_monkey_run.tb_user;
由结果可知，将sex列合并后的结果有三种值：male、female和NULL。
结合COUNT使用时，会自动排除NULL值。
SELECT COUNT(DISTINCT sex) AS sex_num FROM db_monkey_run.tb_user;
多个字段同时会被过滤，即DISTINCT不会单独区分过滤的字段，会针对所有字段进行过滤。
SELECT DISTINCT username,sex FROM db_monkey_run.tb_user;
附件
创建表
CREATE TABLE `db_monkey_run`.`tb_user_score` (
`id` INT NOT NULL AUTO_INCREMENT COMMENT '主键',
`userid` VARCHAR(45) NULL COMMENT '用户id',
`username` VARCHAR(45) NULL COMMENT '用户姓名',
`subject` VARCHAR(45) NULL COMMENT '科目',
`score` DOUBLE NULL COMMENT '分数',
PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf32
COMMENT = '用户各科目得分';
添加数据
INSERT INTO db_monkey_run.tb_user_score(userid,username,subject,score) VALUES ('0x001','xiaoyi','科目一',91);
INSERT INTO db_monkey_run.tb_user_score(userid,username,subject,score) VALUES ('0x001','xiaoyi','科目二',95);
INSERT INTO db_monkey_run.tb_user_score(userid,username,subject,score) VALUES ('0x001','xiaoyi','科目三',77);
INSERT INTO db_monkey_run.tb_user_score(userid,username,subject,score) VALUES ('0x002','xiaoer','科目一',67);
INSERT INTO db_monkey_run.tb_user_score(userid,username,subject,score) VALUES ('0x002','xiaoer','科目二',89);
INSERT INTO db_monkey_run.tb_user_score(userid,username,subject,score) VALUES ('0x002','xiaoer','科目三',89);
INSERT INTO db_monkey_run.tb_user_score(userid,username,subject,score) VALUES ('0x003','xiaosan','科目一',66);
INSERT INTO db_monkey_run.tb_user_score(userid,username,subject,score) VALUES ('0x003','xiaosan','科目二',98);
INSERT INTO db_monkey_run.tb_user_score(userid,username,subject,score) VALUES ('0x003','xiaosan','科目三',76);
INSERT INTO db_monkey_run.tb_user_score(userid,username,subject,score) VALUES ('0x003','xiaosan','科目四',79);