規劃查詢

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     |
+----------+--------------------+-------+
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 報表製作

一起來練習吧！

SQL 報表製作

Preparing Video For Download...