当前位置: 首页>数据库>正文

postgresql中两张表的聚合函数合并到一列或一行,做除法,并保留两位小数

--两张表的无关数据合并到一张表

SELECT A.name, B.name FROM (select o.name, row_number() over(order by name) from tb_org as o) A   FULL JOIN  (select r.name, row_number() over(order by r.name) from tb_region as r) B ON A.row_number = B.row_number;

 

这里是利用了每张表必然有行数,pg在windows下使用row_number()时,必须与 over()一起使用,也就是排序方式,由于少的一列行数为null了,后面的都是乱序

--两张表的聚合函数合并到一列 select count(name) c1 from tb_org union all select count(name) c2 from tb_region 

 

--两张表的聚合函数合并到一行

select * from (select count(name)from tb_org) A,(select count(name) from tb_region ) B

select *,round(c1::numeric /c2,2) from (select count(name) c1 from tb_org) A,(select count(name) c2 from tb_region ) B

或者利用行数 row_number() SELECT count(A.name) c1, count(B.name) c2 FROM (select o.name, row_number() over(order by name) from tb_org as o) A   FULL JOIN  (select r.name, row_number() over(order by r.name) from tb_region as r) B ON A.row_number = B.row_number;

 

--两张无关表的聚合函数,做除法,并保留两位小数 SELECT count(A.name) c1, count(B.name) c2, round(count(A.name)::numeric /count(B.name),2) c3 FROM (select o.name, row_number() over(order by name) from tb_org as o) A   FULL JOIN  (select r.name, row_number() over(order by r.name) from tb_region as r) B ON A.row_number = B.row_number;

postgresql中两张表的聚合函数合并到一列或一行,做除法,并保留两位小数,第1张


https://www.xamrdz.com/database/69n1932719.html

相关文章: