CoderPath

第二章练习答案 ​

本页包含第二章所有练习的答案和详细解释。

01-aggregate ​

练习 1：基本计数 ​

答案：

sql
SELECT COUNT(*) AS total_orders FROM orders;

输出：

text
 total_orders
--------------
            7

练习 2：去重计数 ​

答案：

sql
SELECT COUNT(DISTINCT product) AS unique_products FROM orders;

输出：

text
 unique_products
-----------------
               7

练习 3：求和 ​

答案：

sql
SELECT SUM(amount) AS total_sales FROM orders WHERE amount IS NOT NULL;

输出：

text
 total_sales
-------------
     2055.00

练习 4：平均值 ​

答案：

sql
SELECT ROUND(AVG(amount), 2) AS average_order FROM orders WHERE amount IS NOT NULL;

输出：

text
 average_order
---------------
        293.57

练习 5：最值 ​

答案：

sql
SELECT 
    MIN(amount) AS smallest_order,
    MAX(amount) AS largest_order
FROM orders
WHERE amount IS NOT NULL;

输出：

text
 smallest_order | largest_order
----------------+---------------
          10.00 |       1200.00

练习 6：组合统计 ​

答案：

sql
SELECT 
    COUNT(*) AS total_orders,
    COUNT(DISTINCT customer_name) AS unique_customers,
    SUM(amount) AS total_sales,
    ROUND(AVG(amount), 2) AS average_order
FROM orders
WHERE amount IS NOT NULL;

输出：

text
 total_orders | unique_customers | total_sales | average_order
--------------+------------------+-------------+---------------
            7 |                4 |     2055.00 |        293.57

练习 7：条件聚合 ​

答案：

sql
SELECT 
    COUNT(*) AS large_orders,
    SUM(amount) AS total
FROM orders
WHERE amount > 100;

输出：

text
 large_orders |  total
--------------+---------
            4 | 2000.00

练习 8：NULL 处理 ​

答案：

首先插入一行 amount 为 NULL 的数据：

sql
INSERT INTO orders (customer_name, product_category, product, amount, order_date) VALUES
    ('Eve', 'Electronics', 'Cable', NULL, '2026-01-22');

然后查询：

sql
SELECT 
    COUNT(*) AS count_all,
    COUNT(amount) AS count_amount,
    COUNT(*) - COUNT(amount) AS null_count
FROM orders;

输出：

text
 count_all | count_amount | null_count
-----------+--------------+------------
         8 |            7 |          1

解释：COUNT(*) 计数所有行（包括 NULL），COUNT(amount) 只计数非 NULL 行。

02-group-by ​

练习 1：基本分组 ​

答案：

sql
SELECT 
    product_category,
    COUNT(*) AS order_count
FROM orders
GROUP BY product_category;

输出：

text
 product_category | order_count
------------------+-------------
 Electronics      |           5
 Furniture        |           3

练习 2：分组聚合 ​

答案：

sql
SELECT 
    customer_name,
    SUM(amount) AS total_spent
FROM orders
WHERE amount IS NOT NULL
GROUP BY customer_name
ORDER BY total_spent DESC;

输出：

text
 customer_name | total_spent
---------------+-------------
 Alice         |     1525.00
 Charlie       |      300.00
 Bob           |      230.00

练习 3：多列分组 ​

答案：

sql
SELECT 
    customer_name,
    product_category,
    COUNT(*) AS orders,
    SUM(amount) AS total
FROM orders
WHERE amount IS NOT NULL
GROUP BY customer_name, product_category
ORDER BY customer_name, product_category;

输出：

text
 customer_name | product_category | orders |  total
---------------+------------------+--------+---------
 Alice         | Electronics      |      2 | 1225.00
 Alice         | Furniture        |      1 |  300.00
 Bob           | Electronics      |      1 |   80.00
 Bob           | Furniture        |      1 |  150.00
 Charlie       | Electronics      |      1 |  250.00
 Charlie       | Furniture        |      1 |   50.00

练习 4：带过滤的分组 ​

答案：

sql
SELECT 
    customer_name,
    COUNT(*) AS order_count
FROM orders
WHERE amount > 100
GROUP BY customer_name;

输出：

text
 customer_name | order_count
---------------+-------------
 Alice         |           2
 Bob           |           1
 Charlie       |           1

练习 5：最值分组 ​

答案：

sql
SELECT 
    product_category,
    MIN(amount) AS min_order,
    MAX(amount) AS max_order
FROM orders
WHERE amount IS NOT NULL
GROUP BY product_category;

输出：

text
 product_category | min_order | max_order
------------------+-----------+-----------
 Electronics      |     10.00 |   1200.00
 Furniture        |     50.00 |    300.00

练习 6：表达式分组 ​

答案：

sql
SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(MONTH FROM order_date) AS month,
    COUNT(*) AS orders,
    SUM(amount) AS total_sales
FROM orders
WHERE amount IS NOT NULL
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY year, month;

或更简洁：

sql
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS orders,
    SUM(amount) AS total_sales
FROM orders
WHERE amount IS NOT NULL
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

输出：

text
       month        | orders | total_sales
--------------------+--------+-------------
 2026-01-01         |      7 |     2055.00

练习 7：NULL 处理 ​

答案：

sql
-- 插入 NULL customer_name
INSERT INTO orders (customer_name, product_category, product, amount, order_date) VALUES
    (NULL, 'Electronics', 'Adapter', 15.00, '2026-01-23');

-- 观察分组
SELECT 
    customer_name,
    COUNT(*) AS orders
FROM orders
GROUP BY customer_name
ORDER BY customer_name NULLS LAST;

输出：

text
 customer_name | orders
---------------+--------
 Alice         |      3
 Bob           |      2
 Charlie       |      2
 Eve           |      1
               |      1

解释：NULL 被当作一个单独的组。

练习 8：复杂查询 ​

答案：

sql
SELECT 
    customer_name,
    COUNT(*) AS electronics_orders,
    SUM(amount) AS total
FROM orders
WHERE product_category = 'Electronics' AND amount IS NOT NULL
GROUP BY customer_name
ORDER BY total DESC;

输出：

text
 customer_name | electronics_orders |  total
---------------+--------------------+---------
 Alice         |                  2 | 1225.00
 Charlie       |                  1 |  250.00
 Bob           |                  1 |   80.00

03-having ​

练习 1：基本 HAVING ​

答案：

sql
SELECT 
    customer_name,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_name
HAVING COUNT(*) > 2;

输出：

text
 customer_name | order_count
---------------+-------------
 Alice         |           3

练习 2：金额过滤 ​

答案：

sql
SELECT 
    customer_name,
    SUM(amount) AS total
FROM orders
WHERE amount IS NOT NULL
GROUP BY customer_name
HAVING SUM(amount) > 200
ORDER BY total DESC;

输出：

text
 customer_name |  total
---------------+---------
 Alice         | 1525.00
 Charlie       |  300.00
 Bob           |  230.00

练习 3：平均值过滤 ​

答案：

sql
SELECT 
    customer_name,
    COUNT(*) AS orders,
    ROUND(AVG(amount), 2) AS average_order
FROM orders
WHERE amount IS NOT NULL
GROUP BY customer_name
HAVING AVG(amount) > 150;

输出：

text
 customer_name | orders | average_order
---------------+--------+---------------
 Alice         |      3 |        508.33
 Charlie       |      2 |        150.00

练习 4：WHERE + HAVING ​

答案：

sql
SELECT 
    customer_name,
    COUNT(*) AS electronics_orders
FROM orders
WHERE product_category = 'Electronics'
GROUP BY customer_name
HAVING COUNT(*) >= 2;

输出：

text
 customer_name | electronics_orders
---------------+--------------------
 Alice         |                  2

练习 5：多条件 HAVING ​

答案：

sql
SELECT 
    customer_name,
    COUNT(*) AS orders,
    SUM(amount) AS total
FROM orders
WHERE amount IS NOT NULL
GROUP BY customer_name
HAVING COUNT(*) >= 2 AND SUM(amount) >= 300;

输出：

text
 customer_name | orders |  total
---------------+--------+---------
 Alice         |      3 | 1525.00
 Charlie       |      2 |  300.00

练习 6：分类统计 ​

答案（使用子查询）：

sql
SELECT 
    product_category,
    customer_name,
    COUNT(*) AS orders
FROM orders
GROUP BY product_category, customer_name
ORDER BY product_category, orders DESC;

然后手动取每个类别的前 2 名，或使用窗口函数（第四章内容）。

练习 7：对比查询 ​

答案：

使用 WHERE（更高效）：

sql
SELECT 
    customer_name,
    COUNT(*) AS orders,
    SUM(amount) AS total
FROM orders
WHERE customer_name = 'Alice'
GROUP BY customer_name;

使用 HAVING（低效）：

sql
SELECT 
    customer_name,
    COUNT(*) AS orders,
    SUM(amount) AS total
FROM orders
GROUP BY customer_name
HAVING customer_name = 'Alice';

两者结果相同，但 WHERE 更快（在分组前过滤）。

练习 8：实际场景 ​

答案（需要子查询）：

sql
SELECT 
    o.product_category,
    o.customer_name,
    AVG(o.amount) AS customer_avg
FROM orders o
WHERE o.amount IS NOT NULL
GROUP BY o.product_category, o.customer_name
HAVING AVG(o.amount) > (
    SELECT AVG(amount) 
    FROM orders 
    WHERE product_category = o.product_category AND amount IS NOT NULL
);

这会找出每个类别中，平均订单金额超过该类别整体平均值的客户。

04-multi-group ​

练习 1：基本 ROLLUP ​

答案：

sql
SELECT 
    product_category,
    customer_name,
    COUNT(*) AS orders,
    SUM(amount) AS total
FROM orders
WHERE amount IS NOT NULL
GROUP BY ROLLUP(product_category, customer_name)
ORDER BY product_category NULLS LAST, customer_name NULLS LAST;

输出：

text
 product_category | customer_name | orders |  total
------------------+---------------+--------+---------
 Electronics      | Alice         |      2 | 1225.00
 Electronics      | Bob           |      1 |   80.00
 Electronics      | Charlie       |      1 |  250.00
 Electronics      |               |      4 | 1555.00
 Furniture        | Alice         |      1 |  300.00
 Furniture        | Bob           |      1 |  150.00
 Furniture        | Charlie       |      1 |   50.00
 Furniture        |               |      3 |  500.00
                  |               |      7 | 2055.00

练习 2：识别小计 ​

答案：

sql
SELECT 
    product_category,
    customer_name,
    COUNT(*) AS orders,
    SUM(amount) AS total,
    GROUPING(product_category) AS is_cat_total,
    GROUPING(customer_name) AS is_cust_total
FROM orders
WHERE amount IS NOT NULL
GROUP BY ROLLUP(product_category, customer_name);

练习 3：美化输出 ​

答案：

sql
SELECT 
    CASE 
        WHEN GROUPING(product_category) = 1 THEN '【总计】'
        ELSE product_category 
    END AS category,
    CASE 
        WHEN GROUPING(customer_name) = 1 AND GROUPING(product_category) = 0 THEN '【小计】'
        WHEN GROUPING(customer_name) = 1 THEN ''
        ELSE customer_name 
    END AS customer,
    COUNT(*) AS orders,
    SUM(amount) AS total
FROM orders
WHERE amount IS NOT NULL
GROUP BY ROLLUP(product_category, customer_name)
ORDER BY product_category NULLS LAST, customer_name NULLS LAST;

练习 4：三维 ROLLUP ​

答案：

sql
SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    product_category,
    customer_name,
    COUNT(*) AS orders,
    SUM(amount) AS total
FROM orders
WHERE amount IS NOT NULL
GROUP BY ROLLUP(
    EXTRACT(YEAR FROM order_date),
    product_category,
    customer_name
)
ORDER BY year, product_category, customer_name;

练习 5：CUBE 对比 ​

答案（PostgreSQL）：

sql
-- ROLLUP
SELECT product_category, customer_name, SUM(amount) 
FROM orders WHERE amount IS NOT NULL
GROUP BY ROLLUP(product_category, customer_name);

-- CUBE
SELECT product_category, customer_name, SUM(amount) 
FROM orders WHERE amount IS NOT NULL
GROUP BY CUBE(product_category, customer_name);

CUBE 会额外生成"按客户汇总（跨所有类别）"的行。

练习 6：GROUPING SETS ​

答案（PostgreSQL）：

sql
SELECT 
    product_category,
    customer_name,
    SUM(amount) AS total
FROM orders
WHERE amount IS NOT NULL
GROUP BY GROUPING SETS (
    (product_category),
    (customer_name),
    ()
);

练习 7：部分 ROLLUP ​

答案：

sql
SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    product_category,
    COUNT(*) AS orders,
    SUM(amount) AS total
FROM orders
WHERE amount IS NOT NULL
GROUP BY EXTRACT(YEAR FROM order_date), ROLLUP(product_category);

练习 8：实际报表 ​

答案：

sql
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    product_category,
    SUM(amount) AS total,
    CASE 
        WHEN GROUPING(DATE_TRUNC('month', order_date)) = 1 THEN '总计'
        WHEN GROUPING(product_category) = 1 THEN '月度小计'
        ELSE '明细'
    END AS level
FROM orders
WHERE amount IS NOT NULL
GROUP BY ROLLUP(DATE_TRUNC('month', order_date), product_category)
ORDER BY month, product_category;

复习要点 ​

第二章关键概念 ​

  1. 聚合函数：COUNT、SUM、AVG、MIN、MAX
  2. GROUP BY：创建分组，对每组分别聚合
  3. HAVING：过滤分组结果（基于聚合）
  4. ROLLUP：生成小计和总计

执行顺序 ​

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

WHERE vs HAVING ​

  • WHERE：分组前过滤行（原始数据）
  • HAVING：分组后过滤组（聚合结果）

常见陷阱 ​

  • ❌ WHERE 中使用聚合函数 → ✅ 用 HAVING
  • ❌ SELECT 混用聚合和非聚合列 → ✅ 用 GROUP BY
  • ❌ 用 HAVING 过滤非聚合条件 → ✅ 用 WHERE（更高效）

返回第二章概览 · 返回SQL 学习路线

用心记录每一步成长

Copyright © 2026-present Twiink