创建数据分组
Oracle SQL 入门
Hadrien Lacroix
Content Developer
数据分组
分组信息
| Composer | Milliseconds |
|-----------------|--------------|
| Antonio Vivaldi | 199,086 |
| Pearl Jam | 122,801 |
| Pearl Jam | 65,593 |
| Jimmy Page | 401,920 |
| Jimmy Page | 386,063 |
| Jimmy Page | 132,702 |
| Jimmy Page | 189,675 |
| Jimmy Page | 126,641 |
| Carlos Santana | 318,432 |
| Carlos Santana | 296,437 |
| Carlos Santana | 882,834 |
| ... | ... |
每位作曲家的曲目平均时长是多少？
GROUP BY- 将表中的行分成组
- 使用分组函数汇总每组信息
GROUP BY
SELECT Composer, AVG(Milliseconds)
FROM Track
GROUP BY Composer
| Composer | AVG(Milliseconds) |
|-----------------|-------------------|
| Antonio Vivaldi | 199,086.0 |
| Pearl Jam | 94,197.0 |
| Jimmy Page | 474,888.3 |
| Carlos Santana | 499,234.3 |
| ... | ... |
GROUP BY 与 WHERE
SELECT Composer, AVG(Milliseconds)
FROM Track
WHERE Genre = 1
GROUP BY Composer
| Composer | AVG(Milliseconds) |
|-----------------|-------------------|
| Antonio Vivaldi | 199,086.0 |
| Pearl Jam | 94,197.0 |
| Jimmy Page | 474,888.3 |
| Carlos Santana | 499,234.3 |
| ... | ... |
GROUP BY 与 ORDER BY
SELECT Composer, AVG(Milliseconds) AS Average
FROM Track
GROUP BY Composer
ORDER BY AVG(Milliseconds)
| Composer | AVG(Milliseconds) |
|-----------------|-------------------|
| Pearl Jam | 94,197.0 |
| Antonio Vivaldi | 199,086.0 |
| Jimmy Page | 474,888.3 |
| Carlos Santana | 499,234.3 |
| ... | ... |
GROUP BY 与 ORDER BY
SELECT Composer, AVG(Milliseconds) AS Average
FROM Track
GROUP BY Composer
ORDER BY 2
| Composer | AVG(Milliseconds) |
|-----------------|-------------------|
| Pearl Jam | 94,197.0 |
| Antonio Vivaldi | 199,086.0 |
| Jimmy Page | 474,888.3 |
| Carlos Santana | 499,234.3 |
| ... | ... |
GROUP BY 与 ORDER BY
SELECT Composer, AVG(Milliseconds) AS Average
FROM Track
GROUP BY Composer
ORDER BY Average
| Composer | Average |
|-----------------|-------------------|
| Pearl Jam | 94,197.0 |
| Antonio Vivaldi | 199,086.0 |
| Jimmy Page | 474,888.3 |
| Carlos Santana | 499,234.3 |
| ... | ... |
规范
SELECT 语句中任何非聚合函数的列或表达式，必须出现在 GROUP BY 子句中
SELECT Composer, AVG(Milliseconds), UnitPrice
FROM Track
GROUP BY Composer
column "track.unitprice" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2: SELECT Composer, AVG(Milliseconds), UnitPrice
规范
SELECT 列表中任何非聚合函数的列或表达式，必须出现在 GROUP BY 子句中
SELECT Composer, AVG(Milliseconds), MAX(UnitPrice)
FROM Track
GROUP BY Composer
| Composer | AVG(Milliseconds) | MAX(UnitPrice) |
|-----------------|-------------------|----------------|
| Antonio Vivaldi | 199,086.0 | 0.99 |
| Pearl Jam | 94,197.0 | 0.99 |
| Jimmy Page | 474,888.3 | 0.99 |
| Carlos Santana | 499,234.3 | 0.99 |
| ... | ... | ... |
规范
GROUP BY 中的表达式不必出现在 SELECT 语句中
SELECT AVG(Milliseconds)
FROM Track
GROUP BY Composer
| AVG(Milliseconds) |
|-------------------|
| 199,086.0 |
| 94,197.0 |
| 474,888.3 |
| 499,234.3 |
| ... |
多列分组
SELECT Country, City, COUNT(CustomerId)
FROM Customer
GROUP BY Country, City
| Country | City | COUNT(CustomerId) |
|-----------|---------------------|-------------------|
| Argentina | Buenos Aires | 1 |
| Australia | Sidney | 1 |
| Austria | Vienne | 1 |
| Belgium | Brussels | 1 |
| Brazil | Brasilia | 1 |
| Brazil | São José dos Campos | 1 |
| Brazil | São Paulo | 2 |
| ... | ... | ... |
让我们来练习！
Oracle SQL 入门
