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;