sql - SQL 显示由其他值计算的一个结果？

Question

看来我们可以使用SQL如下语句：

select
  (
    select
      count(*) as c_foos
    from
      foos
  ),
  (
    select
      count(*) as c_bars
    from
      bars
  );

但我们做不到

select
  (
    select
      count(*) as c_foos
    from
      foos
  ),
  (
    select
      count(*) as c_bars
    from
      bars
  ),
  (
    select
      (c_foos / c_bars) as the_ratio
  );

或者

select
  (
    select
      count(*) as c_foos
    from
      foos
  ),
  (
    select
      count(*) as c_bars
    from
      bars
  ),
  (c_foos / c_bars) as the_ratio;

有没有办法显示所有 3 个数字？有什么可以做什么不能做的更明确的规则吗？

标签： sqlpostgresqlcommon-table-expression

Answer 1

你可以试试这个：

您在一个子句中定义了两个 CTE WITH，因此您可以在基于两个 cte 表（cte_num和cte_den）的主查询中使用您的结果

WITH recursive
    cte_num AS (
    SELECT count(*) as c_foos
    FROM foos
),
cte_den AS (
    SELECT count(*) as c_bars
    FROM bars
)
SELECT
    cte_num.foos,
    cte_den.bars,
    cte_num.foos / cte_den.bars as the_ratio
from cte_num, cte_den;