规划查询

SQL 报表制作

Tyler Pernes

Learning & Development Consultant

本章目标

SQL 报表制作

需要思考的问题

  • 我们需要从哪些表获取数据？
  • 我们应如何合并这些表？
  • 我们需要创建哪些字段？
  • 需要包含哪些筛选？
  • 是否需要排序或限制？
SQL 报表制作

情景

Gold Medals by Demographic Group
(Western European Countries Only)
+----------+--------------------+-------+
| season   |  demographic_group | golds |
|----------|--------------------|-------|
| Winter   | Male Age 26+       | 13    |
| Winter   | Female Age 26+     | 8     |
| Summer   | Male Age 13-25     | 7     |
| Summer   | Female Age 13-25   | 6     |
| Winter   | Male Age 13-25     | 4     |
| Summer   | Male Age 26+       | 4     |
| Winter   | Female Age 13-25   | 4     |
| Summer   | Female Age 26+     | 2     |
+----------+--------------------+-------+
SQL 报表制作

1 - 我们需要从哪些表获取数据？

SQL 报表制作

2 - 我们应如何合并这些表？

SQL 报表制作

2 - 我们应如何合并这些表？

SQL 报表制作

2 - 我们应如何合并这些表？

SQL 报表制作

2 - 我们应如何合并这些表？

SQL 报表制作

3 - 我们需要创建哪些字段？

+----------+--------------------+-------+
| season   |  demographic_group | golds |
|----------|--------------------|-------|
| Winter   | Male Age 26+       | 13    |
| Winter   | Female Age 26+     | 8     |
| Summer   | Male Age 13-25     | 7     |
| Summer   | Female Age 13-25   | 6     |
| Winter   | Male Age 13-25     | 4     |
| Summer   | Male Age 26+       | 4     |
+----------+--------------------+-------+
  • season - static string
  • demographic_group - conditional
  • golds - SUM()
SQL 报表制作

4 - 需要包含哪些筛选条件？

Gold Medals by Demographic Group
(Western European Countries Only)
+----------+--------------------+-------+
| season   |  demographic_group | golds |
|----------|--------------------|-------|
| Winter   | Male Age 26+       | 13    |
| Winter   | Female Age 26+     | 8     |
| Summer   | Male Age 13-25     | 7     |
| Summer   | Female Age 13-25   | 6     |
| Winter   | Male Age 13-25     | 4     |
| Summer   | Male Age 26+       | 4     |
+----------+--------------------+-------+
  • WHERE 还是 HAVING？
  • 维度筛选用 WHERE
SQL 报表制作

5 - 是否需要排序或限制？

Gold Medals by Demographic Group
(Western European Countries Only)
+----------+--------------------+-------+
| season   |  demographic_group | golds |
|----------|--------------------|-------|
| Winter   | Male Age 26+       | 13    |
| Winter   | Female Age 26+     | 8     |
| Summer   | Male Age 13-25     | 7     |
| Summer   | Female Age 13-25   | 6     |
| Winter   | Male Age 13-25     | 4     |
| Summer   | Male Age 26+       | 4     |
+----------+--------------------+-------+
  • 不需要 LIMIT
  • 按 golds 降序排序
SQL 报表制作

Passons à la pratique !

SQL 报表制作

Preparing Video For Download...