ch05: SQL高级处理

练习题

5.1

请说出针对本章中使用的 product（商品）表执行如下 SELECT 语句所能得到的结果。

SELECT  product_id
       ,product_name
       ,sale_price
       ,MAX(sale_price) OVER (ORDER BY product_id) AS Current_max_price
  FROM product;

这里使用了聚合函数MAX（），结果为累计的聚合函数值。Current_max_price列为按product_id顺序升序，同product_id下的最大值。

5.2

继续使用product表，计算出按照登记日期（regist_date）升序进行排列的各日期的销售单价（sale_price）的总额。排序是需要将登记日期为NULL 的“运动 T 恤”记录排在第 1 位（也就是将其看作比其他日期都早）

SELECT
    product_id,
    product_name,
    sale_price,
    regist_date,
    SUM(sale_price) OVER(ORDER BY regist_date) AS current_sum_price
FROM
    product;

结果如下：

5.3

思考题

① 窗口函数不指定PARTITION BY的效果是什么？

A: 不会对PARTITION BY指定的组别进行操作，而是对全表进行操作。

② 为什么说窗口函数只能在SELECT子句中使用？实际上，在ORDER BY 子句使用系统并不会报错。

A：因为窗口函数是对WHERE子句或者GROUP BY子句处理后的“结果”进行的操作，在SELECT子句之外使用窗口函数是没有意义的。

5.4

使用简洁的方法创建20个与 shop.product 表结构相同的表，如下图所示：

 

  ○ begin
    -- 定义循环变量
    declare num int;
    set num = 1;

    -- 循环20遍
    while num <= 20 do
        set @table_name = concat('table', if(num < 10, concat('0', num), num));

        -- 定义建表语句
        set @sql_begin = 'create table if not exists ';
        set @sql_end = ' like product;';
        -- 拼接完整的sql语句
        set @create_sql = concat(@sql_begin, @table_name, @sql_end);

        -- 定义预处理过程
        prepare create_table from @create_sql;
        execute create_table;
        deallocate prepare create_table;

        set num = num + 1;
    end while;
end;

call create_test_table();
  ○ 执行：
  ○ call create_test_table
  ○ 删除存储过程：
  ○ drop procedure create_test_table;