博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
PostgreSQL使用with一例
阅读量:5740 次
发布时间:2019-06-18

本文共 1133 字,大约阅读时间需要 3 分钟。

  hot3.png

经常在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;
 
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结果虽然取出来了,但并没有排序,没有达到效果
解决办法:
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_nameorder 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 tmpon t2.user_id = tmp.user_id order by tmp.num desc

分析:

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

转载于:https://my.oschina.net/Kenyon/blog/103254

你可能感兴趣的文章
SCOM 2012系列③导入管理包
查看>>
读《做单--成交的秘密》有感
查看>>
shell脚本如何监控程序占用带宽?
查看>>
如何定位cpu占用率高的java线程?
查看>>
GRE over IPSEC 同时NAT-T(Profile PAT)
查看>>
Excel 2016中的新增函数之SWITCH
查看>>
Python的几种实现
查看>>
C#反射的Assembly的简单应用
查看>>
《鸟哥的Linux私房菜》7章 Linux文件与目录管理 习题答案
查看>>
请投资自己,不会花钱的人没前途
查看>>
C#基础知识整理:基础知识(4) 继承
查看>>
perl注释多行
查看>>
Service Manager R2管理服务器安装
查看>>
一道面试问题学生们的多种解答汇总
查看>>
Windows Server 2012正式版RDS系列④
查看>>
企业长寿的乔氏秘诀
查看>>
NOSQL系列-Redis精简版安装与Ruby测试
查看>>
【VMware混合云】应用为王
查看>>
Photoshop制作一个质感的卡通小公仔
查看>>
为小米手环的定价叫好
查看>>