Grouping by One Column 按一列分组

  1. SELECT
  2.     Continent,
  3.     COUNT(*) AS CountriesCount,
  4.     MIN(Population) AS MinPopulation,
  5.     MAX(Population) AS MaxPopulation,
  6.     AVG(Population) AS AvgPopulation
  7. FROM country
  8. GROUP BY Continent

Grouping by Multiple Columns 按多列分组

SELECT
    Continent,
    Region,
    COUNT(*) AS CountriesCount,
    AVG(Population) AS AvgPopulation
FROM country
GROUP BY Continent, Region

Grouping by Column Number 按列号分组

SELECT
    Continent,
    Region,
    COUNT(*) AS CountriesCount,
    AVG(Population) AS AvgPopulation
FROM country
GROUP BY 1, 2

Filtering Groups 过滤组

SELECT
    Continent,
    COUNT(*) AS CountriesCount
FROM country
GROUP BY Continent
HAVING COUNT(*) > 20

Having and Where in One Statement 在一个陈述中有和在哪里

SELECT
    Continent,
    COUNT(*) AS CountriesCount
FROM country
WHERE Population >= 1000000
GROUP BY Continent
HAVING COUNT(*) >= 20

Grouping and Sorting 分组和排序

SELECT
    Continent,
    COUNT(*) AS CountriesCount
FROM country
GROUP BY Continent
HAVING COUNT(*) > 20
ORDER BY CountriesCount

Using Partition BY 使用分区方式

SELECT
Name,
Population,
AVG(Population) OVER (PARTITION BY CountryCode) AS AvgPopulation
FROM world.city