postgreSQL 使用With字句的例子

一时失言乱红尘 2021-09-10 13:16 399阅读 0赞

经常在SQL中会遇到比较复杂的SQL，很多老手也不知道怎么入手。在Postgres数据库中有一种强大的with用法，可以分拆复杂的SQL并重新组装，也可以当做临时表来使用，之前也曾用with语法来实现SQL层面的递归。以下介绍开发中遇到的另一个例子。   
  
环境： Postgres 9.1.2   
评论表T1(user\_id reference T2(user\_id))   
用户表T2(user\_id)   
  
场景：    
需要对T1表中数据按评论数分组排序，选择前5条记录与表T2进行关联，返回满足条件的T2用户数据，但要根据T1的排序结果来展示，也就是要显示评论最多的5个用户的详细信息,并按评论数把用户从高到低排列。   
  
开发的SQL：   
1.查看表T1，并排序

select user_id,count(1) from t1 group by user_id order by  count(1) desc limit 5;

[![154516_bhDU_267373.jpg][]][154516_bhDU_267373.jpg]    
  
2.与表T2关联

select * from t2 where user_id in(
    select user_id from t1 group by user_id order by  count(1) desc limit 5);

但是展示的T2结果虽然取出来了，但并没有排序，没有达到效果   
[![154627_SSOx_267373.jpg][]][154627_SSOx_267373.jpg]    
  
解决办法：    
1.使用两个表的join来实现

select t2.user_id,t2.user_name,count(1) as num from t1,t2 
    where t1.user_id = t2.user_id 
    group by t2.user_id,t2.user_name
    order by num desc limit 5;

2.用with来构造：

with tmp as(
    select user_id,count(1) as num from t1 group by user_id order by  num desc limit 5
    )
    select t2.*,tmp.num from t2 inner join tmp
    on t2.user_id = tmp.user_id order by tmp.num desc

分析：   
第一种办法需要两张表关联再分组取前5条，执行计划如下：  
  
[![162327_C84g_267373.jpg][]][162327_C84g_267373.jpg]  
如果数据量比较大，尤其是T2表很大的时候，会消耗比较多的资源，另外如果想取用户表里有其他的字段，也需要进行分组  
第二种办法是采用with来构造临时表，然后再去与T2表关联取数，执行计划如下：   
  
[![162339_fVTc_267373.jpg][]][162339_fVTc_267373.jpg]  
可以看到消耗的资源相对少一点，测试中发现T2、T1表很大的时候，这种差距尤其明显。

[154516_bhDU_267373.jpg]: /images/20210726/cfb64fcb37ed4834827ad281b9e092be.png
[154627_SSOx_267373.jpg]: /images/20210726/e50a24fdf9c7479295628bb5bd0a7a21.png
[162327_C84g_267373.jpg]: /images/20210726/7628ac8f0f0b40d89bb6260065ca5eb1.png
[162339_fVTc_267373.jpg]: /images/20210726/3bc46d3e2bed4768a2b9099d33fcb6f0.png