SQL 每日一练 001

目录

01 题目

02 思路

03 答案

04 知识点

05 测试语句

---

01 题目

有如下一张表F0812

productBar为产品编码，step为组装序号，timeUsed为组装用时。

也就是一个产品必须要经过1,2,3,4个步骤，才算组装成功，像2001000001为成功，2001000002为失败

查询出每个产品是否组装成功，以及他们的组装用时。

预计得到如下结果：

02 思路

1. 查询每个产品，是需要分组；是否组装成功，是一个判断；组装用时是一个求和

2. 需要经过4个步骤才算组装成功，所以考虑用组装序号最大值作为判断条件

3. 组装用时，求和，考虑到直接分组用SUM函数

03 答案

我的答案

 SELECT 
     productBar AS 产品编码, 
     (CASE WHEN MAX(step)=4 THEN '成功' ELSE '失败' END) AS 组装序号状态,
     SUM(timeUsed) AS 组装用时
 FROM 
     `f0812` 
 GROUP BY 
     productBar
     
 -- 做题时的错误点
 -- 1. 成功，失败，是字符串，要加引号

他人答案

需要经过四个步骤，也就意味着 数量 = 4 可以使用 count求数量

 SELECT 
     productBar AS 产品编码, 
     (CASE WHEN COUNT(step)=4 THEN '成功' ELSE '失败' END) AS 组装序号状态,
     SUM(timeUsed) AS 组装用时
 FROM 
     `f0812` 
 GROUP BY 
     productBar

补充答案

使用 if 判断替代 CASE 判断

 SELECT 
     productBar AS 产品编码, 
     IF(MAX(step) = 4, '成功','失败') AS 组装序号状态,
     SUM(timeUsed) AS 组装用时
 FROM 
     `f0812` 
 GROUP BY 
     productBar

04 知识点

支点一：分组 -- GROUP BY

用法：

• 按照指定字段对数据进行分组，分组后，可以使用聚集函数对每一组数据进行统计

• 分组依据可以是计算字段，但不能是聚集函数，不能使用COUNT、SUM 作为分组依据

• 嵌套分组，对每一层分组后的数据再进行分组。

  • 用法：在GROUP BY 子句中，按照分组先后顺序用逗号隔开

顺序：

SELECT * FROM 表名 WHERE 条件 GROUP BY 条件 ORDER BY 字段

补充：HAVING

• 与 WHERE 筛选相比

  • WHERE 对原始数据进行筛选，HAVING 筛选分组后的数据

  • GROUP BY 对一次筛选后的结构进行分组，HAVING 选出符合条件的分组作为结果

  • WHERE 子句必须写在GROUP BY 前面，HAVING 子句必须写在GROUP BY 后面

  • WHERE 中不允许使用聚集统计函数，HAVING 子句可以使用函数

支点二：判断 -- IF语句 & CASE 语句

IF 与 CASE

• IF 适合简单的判断

• CASE 可以简化多条件判断语句

IF 语句

IF 关键字的作用类似Excel中的IF公式，根据条件来输出字段的值

-- IF 判断score是否大于等于60，如果结果是True，则score的值是'及格'，
-- 如果结果是False，score的结果是'不及格'
SELECT 
    s_id, 
    s_name, 
    c_name, 
    IF(score>=60, '及格', '不及格') 
FROM 
    student 
    INNER JOIN course 
    LEFT JOIN score

特殊点

• NULL 与任何数字相比较的结果都是NULL，而非 TRUE 或 FALSE

• IF 关键字规定，如果比较结果为NULL，则相当于结果为 FALSE ，所以返回第二个值

CASE 语句

简化多条件判断，根据不同的情况采用不同的处理

  CASE
     WHEN 情况1 THEN A
     WHEN 情况2 THEN B
     WHEN 情况3 THEN C
     ELSE D
   END

05 测试语句

 CREATE TABLE F0812(
     productBar VARCHAR(10),
     step INT,
     timeUsed INT   
 );
 ​
 INSERT INTO F0812 VALUES('2001000001',1,10);
 INSERT INTO F0812 VALUES('2001000001',2,13);
 INSERT INTO F0812 VALUES('2001000001',3,12);
 INSERT INTO F0812 VALUES('2001000001',4,18);
  
 INSERT INTO F0812 VALUES('2001000002',1,11);
 INSERT INTO F0812 VALUES('2001000002',2,31);
 INSERT INTO F0812 VALUES('2001000002',3,23);
  
 INSERT INTO F0812 VALUES('2001000003',1,21);
 INSERT INTO F0812 VALUES('2001000003',2,22);
 INSERT INTO F0812 VALUES('2001000003',3,23);
 INSERT INTO F0812 VALUES('2001000003',4,23);