使用 with as 优化复杂的查询SQL

要为复杂的数据查询需求编写SQL语句， 可以将复杂的需求分解成多个简单的需求，然后逐个解决。这些小需求，就是一个个的简单查询。借助with as语法，又可以将这些简单查询组合成最终需要的复杂查询。

with as语法的基本语法为：with 表名 as (查询语句)。它常常用于查询复杂的数据集，或者需要多次使用同一个子查询的场景。通过with as语法，可以将子查询的结果集存储在临时表中，然后在后续查询中使用这个临时表，从而提高查询效率。同时，with as语法还可以让代码更加易读，可维护性更高。

下面我们通过实际案例，来介绍with as语法的妙用。

假设我们有一个订单表，其中包含订单id、商品id、购买数量、价格等信息。一个商品表，包含商品id、商品名称、商品分类等信息。我们需要查询每个商品分类中销售额最高的商品信息。可以这样分解：先统计每个商品分类中每个商品的销售额，然后查询出每个商品分类中销售额最高的商品的销售额，最后查询每个商品分类中销售额最高的商品信息。

按照上面分解的小需求，先统计每个商品分类中每个商品的销售额，该查询就是一个普通内连接分组统计。使用with as语法，将查询结果存放在名为sales的临时表：

with sales as (
    select g.category, o.goods_id, sum(o.quantity * o.price) as total_sales
    from orders o, goods g
    where o.goods_id = g.goods_id
    group by g.category, o.goods_id
​​​​​​​),

然后查询出每个商品分类中销售额最高的商品的销售额，是临时表sales上