一、前言

关于代码的格式问题，其实这个也没有什么好说的，每个人都有不同的习惯，不能说我的方法好，你的方法不好，适合自己的就是最好的。
针对不熟悉的业务，我很喜欢去看相关业务的SQL逻辑，如果能够结合需求或者业务场景来理解，那自然再好不过，要是代码能再给点数据，就更完美了！之前看过了好些同事的SQL代码，有的写的特别的工整，看着特别舒服，当然，更多的是怎么方便怎么来，有时候看得我抓狂，之前看到一个最厉害的，一个逻辑从头写到尾，一行到底，关联了十来个表，子查询、and、where条件穿插其中，没有换行，一个逻辑一行到底这还是头一次见，当事人已经离职，不知道他后期维护是怎么做的，难以想象，哈哈哈。

今天推荐一个语法吧with as()，可以帮助我们写出比较优雅易看的数据。

二、with as语法及应用

这个语法其实是创建一个临时表，和子查询差不多，只不过它可以把代码单独拎出来，而不是放在from或join后面，导致整体看起来有很臃肿的感觉。有点像编程中的函数，把代码封装起来，然后直接调用表名，可以查找返回的字段。
举个小例子来看一下吧：
假设我有一个订单表，现在想看一下所有用户最近购买的订单金额和时间。
为了方便大家拿来就用，我把这些数据通过with as()创建一个临时表来调试，代码如下：

with 
orders as(
    select 100 as "user_id",'2022-07-01 10:11:33' as "payment_time",1240 as "paymnet_amount" union all
    select 101,'2022-07-01 11:35:12',1630 union all
    select 102,'2022-07-01 13:24:45',3304 union all
    select 103,'2022-07-01 16:44:59',1146 union all
    select 104,'2022-07-01 19:01:27',1895 union all
    select 103,'2022-07-11 16:44:59',2146 union all
    select 104,'2022-07-21 19:01:27',1095 union all
    select 104,'2022-08-01 19:01:27',3088 
)
-- 对orders订单进行排序
,orders_order_by as(
    select o.*
        ,row_number()over(partition by o.user_id order by o.payment_time desc) "last_pay_label"
    from orders o 
)
-- 取出最后一次付款的所有信息
select o.user_id,o.payment_time,o.paymnet_amount
from orders_order_by o
where o.last_pay_label=1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

如果是通过子查询，则是这样子：
看着似乎还可以，也不会很冗余。

with 
orders as(
    select 100 as "user_id",'2022-07-01 10:11:33' as "payment_time",1240 as "paymnet_amount" union all
    select 101,'2022-07-01 11:35:12',1630 union all
    select 102,'2022-07-01 13:24:45',3304 union all
    select 103,'2022-07-01 16:44:59',1146 union all
    select 104,'2022-07-01 19:01:27',1895 union all
    select 103,'2022-07-11 16:44:59',2146 union all
    select 104,'2022-07-21 19:01:27',1095 union all
    select 104,'2022-08-01 19:01:27',3088 
)
-- 取出最后一次付款的所有信息
select o.user_id,o.payment_time,o.paymnet_amount
from (
    -- 对orders订单进行排序
    select o.*
        ,row_number()over(partition by o.user_id order by o.payment_time desc) "last_pay_label"
    from orders o 
)o
where o.last_pay_label=1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

附上结果图：

小案例稍微升级一下，这次拿购买至少两次以上的用户最近两次购买的订单总金额和时间间隔。
用with as实现则是：

with 
orders as(
    select 100 as "user_id",'2022-07-01 10:11:33' as "payment_time",1240 as "paymnet_amount" union all
    select 101,'2022-07-01 11:35:12',1630 union all
    select 102,'2022-07-01 13:24:45',3304 union all
    select 103,'2022-07-01 16:44:59',1146 union all
    select 104,'2022-07-01 19:01:27',1895 union all
    select 103,'2022-07-11 16:44:59',2146 union all
    select 104,'2022-07-21 19:01:27',1095 union all
    select 104,'2022-08-01 19:01:27',3088 
)
-- 对orders订单进行排序
,orders_order_by as(
    select o.*,row_number()over(partition by o.user_id order by o.payment_time desc) "last_pay_label"
    from orders o 
)
-- 分别取最后一次o1，和倒数第二次o2订单进行关联
select o1.user_id
    ,o1.paymnet_amount+o2.paymnet_amount "paymnet_amount"
    ,datediff(o1.payment_time,o2.payment_time) "time_diff"
from orders_order_by o1,orders_order_by o2 
where o2.user_id=o1.user_id 
and o1.last_pay_label=1
and o2.last_pay_label=2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

接下来再用子查询查一遍：

with orders as(
    select 100 as "user_id",'2022-07-01 10:11:33' as "payment_time",1240 as "paymnet_amount" union all
    select 101,'2022-07-01 11:35:12',1630 union all
    select 102,'2022-07-01 13:24:45',3304 union all
    select 103,'2022-07-01 16:44:59',1146 union all
    select 104,'2022-07-01 19:01:27',1895 union all
    select 103,'2022-07-11 16:44:59',2146 union all
    select 104,'2022-07-21 19:01:27',1095 union all
    select 104,'2022-08-01 19:01:27',3088 
)
select o1.user_id
    ,o1.paymnet_amount+o2.paymnet_amount "paymnet_amount"
    ,datediff(o1.payment_time,o2.payment_time) "time_diff"
from (
    -- 最后一次订单
    select o.*,row_number()over(partition by o.user_id order by o.payment_time desc) "last_pay_label"
    from orders o 
    ) o1
join (
    -- 倒数第二次订单
    select o.*,row_number()over(partition by o.user_id order by o.payment_time desc) "last_pay_label"
    from orders o 
    ) o2 on o2.user_id=o1.user_id 
where o1.last_pay_label=1
and o2.last_pay_label=2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

附上结果图：

通过以上两个小案例，或许可以感受到with as带来的一些好处。案例一比较简单，所以没有太大的区别，但是随着代码的复杂度提升，子查询会变得越来越“臃肿”，特别在几百上千行的代码中，经常看到select，看不到具体来自于哪一个表，需要来回反复查找。而通过with as可以更快定位到是哪一个表，然后往上找源表，相对来说会整洁而且模块化。另外一个重要的点是，在代码逻辑重复使用的时候，with as重新调一便表名即可，但是子查询需要原原本本复制以便，后期维护也麻烦，需要多出同时修改，如果不是原创者，这可能会成为一个实实在在的坑。

三、总结

总结一下，with as的语法三个优点：模块化、可复用、优雅整洁。
另外，代码必要的注释和空格/空行还是很需要的，可以让自己养一个好习惯，当然仁者见仁智者见智，喜欢便好。

- End -