Navicat 博客

将查询结果分为多个范围 2020 年 8 月 4 日,由 Robert Gravelle 撰写

对于数据库开发人员和数据库管理员(DBA)来说,将查询结果分组到大小相等的存储桶中是常见的要求。示例包括:

  • 姓氏以 A-L 和 M-Z 开头的客户
  • 产品价格介于 1-10 元、11-20 元、21-20 元之间,等等。
  • 季度销售,即 1-3 月、4-6 月、7-9 月、10-12月

标准SQL非常适合此任务。通过将 CASE 语句的功能与 GROUP BY 子句结合使用,可以将数据分解为我们认为能最佳解释数据所需的任何范围。在今天的文章中,我们将在 Navicat Premium 的查询编辑器中编写几个范围查询。

将成绩分成百分位数

我们的第一个示例将需要一个包含几个学生的成绩的表。这是创建 grade 表和数据填充的 SQL:

DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`  (
  `StuID` int(11) NULL DEFAULT NULL,
  `Semester` tinyint(4) NULL DEFAULT NULL,
  `YEAR` int(11) NULL DEFAULT NULL,
  `Marks` int(11) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;


INSERT INTO `grade`(`StuID`, `Semester`, `YEAR`, `Marks`) VALUES (110, 1, 2018, 66);
INSERT INTO `grade`(`StuID`, `Semester`, `YEAR`, `Marks`) VALUES (110, 3, 2018, 77);
INSERT INTO `grade`(`StuID`, `Semester`, `YEAR`, `Marks`) VALUES (110, 2, 2018, 86);
INSERT INTO `grade`(`StuID`, `Semester`, `YEAR`, `Marks`) VALUES (110, 4, 2018, 69);
INSERT INTO `grade`(`StuID`, `Semester`, `YEAR`, `Marks`) VALUES (100, 1, 2018, 20);
INSERT INTO `grade`(`StuID`, `Semester`, `YEAR`, `Marks`) VALUES (100, 2, 2018, 39);
INSERT INTO `grade`(`StuID`, `Semester`, `YEAR`, `Marks`) VALUES (100, 3, 2018, 65);
INSERT INTO `grade`(`StuID`, `Semester`, `YEAR`, `Marks`) VALUES (100, 4, 2018, 70);
INSERT INTO `grade`(`StuID`, `Semester`, `YEAR`, `Marks`) VALUES (99, 1, 2018, 50);
INSERT INTO `grade`(`StuID`, `Semester`, `YEAR`, `Marks`) VALUES (99, 2, 2018, 45);
INSERT INTO `grade`(`StuID`, `Semester`, `YEAR`, `Marks`) VALUES (99, 3, 2018, 90);
INSERT INTO `grade`(`StuID`, `Semester`, `YEAR`, `Marks`) VALUES (99, 4, 2018, 96);

这是 Navicat 中的 grade 表:

grade_table (89K)

假设我们要按相等的百分比象限来计算学生的成绩,如下所示:

  • 0 至 25
  • 26 至 50
  • 51 至 75
  • 76 至 100

这是执行此操作的查询以及生成的结果:

student_marks (60K)

你会注意到 CASE 语句使用 BETWEEN 运算符定义了每个范围。它选择包含范围内的值,这意味着外部值也包含在范围内。BETWEEN 能处理多种类型的数据,包括数字、文本和日期。

处理日期

在许多情况下,可以使用 DATE 类型的许多日期部分函数将日期划分为逻辑段,例如 DAY()、DAYOFMONTH()、DAYOFWEEK()、DAYOFYEAR()、MONTH()、YEAR() 等。 这些函数使你可以通过直观的单位分割范围。

为了演示,这是一个使用 Sakila 示例数据库在MySQL中进行的查询,该查询计算了每个客户的平均租金,并按年份和月份进行了细分:

average_rental_cost (163K)

使用 DATE 函数的优点是它们使我们可以省去 CASE 语句,因为可以使用 GROUP BY 按相同的函数分组。

总结

在今天的文章中,我们学习了如何使用 Navicat Premium 的查询编辑器编写范围查询。如果你对 Navicat Premium 感兴趣,可以免费试用 14 天!

Navicat 文章
频道条目
分享
文章归档